How to Use VLOOKUP for Rows in Excel (With Alternatives)

VLOOKUP function is generally used to look for a value in the leftmost column in a table and the function will return a value in the same row from the specified column. In this article, you will learn how you can use this VLOOKUP function to look up values in rows and extract data based on the specified criteria.

Using VLOOKUP for Rows in Excel: 4 Methods

1. Use of MATCH function to Define Column Number from Rows in VLOOKUP

In the following picture, a dataset is presented with the amounts of sales of several salespersons over consecutive six months in a year. We’ll use the VLOOKUP function here to extract the sales value of a salesman in a specified month.

Although the VLOOKUP function looks for a value in the leftmost column or the vertical range of cells, here we can use the MATCH function to define the column number for the specified month from the month headers ranging from C4 to H4.

Let’s assume that we want to know the sales value of Jordan in the month of April.

In the output Cell E15, the required formula will be:

=VLOOKUP(E13,B5:H11,MATCH(E14,B4:H4,0),FALSE)

After pressing Enter, you’ll get the sales value of Jordan in April at once.

In this formula, the MATCH function defines the column number for the VLOOKUP function. The VLOOKUP function then uses this column number to extract data based on the specified month from the month headers.

2. Use of Multiple Rows with VLOOKUP Function in Excel

Now you’re seeing the dataset with a few modifications. Our data table now represents the comparative sales values over three fixed months in two different years.

In this section, we’ll use the VLOOKUP function for multiple rows containing years and months. For example, we’re going to extract the sales value of Jordan in the month of February 2021.

The required formula with the VLOOKUP and MATCH functions in Cell E17 will be:

=VLOOKUP(E14,B6:H12,MATCH(E16&E15,C4:H4&C5:H5,0)+1,FALSE)

After pressing Enter, you’ll find the sales value under specified conditions.

🔎 How Does the Formula Work?

The use of Ampersand (&) joins the selected month and year from the Cells E15 and E16.
The lookup array in the MATCH function has been defined by an array of pairs containing all years and months joined by the Ampersand (&).
In the lookup array of the MATCH function, the two ranges of cells have been selected starting from Column C. So, by adding ‘1’ to the MATCH function in the third argument of the VLOOKUP function, the formula will consider the index of the return column number based on the entire array of B6:H12.

3. Combining VLOOKUP with Column Function to Return an Entire Row

Let’s go back to our primary dataset. Assuming that we want to know the sales values of a specified salesperson for all months available in the dataset. Here we can combine VLOOKUP with the COLUMN function to get all return values in a single row as the COLUMN function.

📌 Step 1:

➤ Select the output Cell C14 and type the following formula:

=VLOOKUP($C$13,$B$5:$H$11,COLUMN(A1)+1,FALSE)

➤ Press Enter and you’ll get the sales value for Jordan in January.

📌 Step 2:

➤ Now use the Fill Handle from Cell C14 to autofill the next five cells in Row 14.

Thus you’ll get all the sales data for a specified salesman at once.

In this formula, the COLUMN function has been used to alter the column numbers serially in the third argument of the VLOOKUP function while auto-filling the 14th row.

4. Including an Array Formula in VLOOKUP to Extract Rows in Excel

If you want to get all the sales data for a salesperson with a one-step formula then you have to use an array formula to define the column numbers in the VLOOKUP function.

The required array formula with the VLOOKUP function in Cell C14 should look like this:

=VLOOKUP(C13,B5:H11,{2,3,4,5,6,7},FALSE)

After pressing Enter, you’ll get all the sales data for Jordan in a single row right away. You don’t have to go for further steps anymore as shown in the previous method.

In this function, the column numbers have been defined with an array containing the index numbers of the return columns: {2,3,4,5,6,7}. The VLOOKUP function returns the outputs from these specified columns for the specified salesperson.

Two Alternatives to the VLOOKUP While Looking for Rows

1. Use of HLOOKUP Function to Look for Rows in Excel

The HLOOKUP function looks for a value in the top row of a table or array of values and returns the value in the same column from the specified row. So, by using the HLOOKUP function, we can now directly look for a month along with the month headers in a single row. The generic formula of the HLOOKUP function is:

=HLOOKLUP(lookup_value, table_array, row_index_num, [range_lookup])

Since we’re looking for the sales value of Jordan in the month of April, the required formula in Cell E15 will be:

=HLOOKUP(E14,B4:H11,MATCH(E13,B4:B11,0),FALSE)

After pressing Enter, you’ll be shown the sales value of Jordan for the specified month at once.

In this formula, the MATCH function defines the row number of the specified salesperson in Column B.

2. Use of INDEX-MATCH Formula to Lookup Along Columns and Rows

The MATCH function returns the relative position of an item in an array that matches a specific value in a specific order and the INDEX function returns a value of the cell at the intersection of a particular row and a column in a given range. The generic formula of this INDEX function is:

=INDEX(array, row_numer, [column_numer])

Or,

=INDEX(reference, row_num, [column_num], [area_num])

With the use of the MATCH function, we can specify the row and column numbers of the INDEX function for a particular salesman in a particular month to extract the corresponding sales value.

The required formula in the output Cell E15 will be:

=INDEX(B5:H11,MATCH(E13,B5:B11,0),MATCH(E14,B4:H4,0))

Now press Enter and you’ll be displayed the sales value of Jordan in April.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

VLOOKUP for Rows.xlsx

Concluding Words

I hope the examples described above will now help you to apply them in your Excel spreadsheets while using the VLOOKUP function to look for the values in rows. If you have any questions or feedback, please let me know in the comment section.