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 get to learn how you can use this VLOOKUP function to lookup values in rows and extract data based on the specified criteria.

Download Practice Workbook

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

4 Methods of Using VLOOKUP Function for Rows in Excel

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:

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 in 2021.

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

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.

Read More: How to VLOOKUP with Multiple Conditions in Excel

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:

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

📌 Step 2:

➤ Now use 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.

Read More: How to VLOOKUP and Return Multiple Values Vertically in Excel

Similar Readings

• VLOOKUP Not Working (8 Reasons & Solutions)
• What Is a Table Array in VLOOKUP?
• Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)
• SUMIF and VLOOKUP Functions Across Multiple Sheets in Excel
• Excel VLOOKUP to Find Last Value in Column (with Alternatives)

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:

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, so now the required formula in Cell E15 will be:

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 on Column B.

Read More: VLOOKUP from Multiple Columns with Only One Return in Excel

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:

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

Read More: INDEX MATCH vs VLOOKUP Function (9 Examples)

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. Or you can check out our other articles related to Excel functions on this website.

You May Also Like to Explore

• VLOOKUP to Return Multiple Columns in Excel (4 Examples)
• Use of VBA VLOOKUP to Find Values from Another Worksheet in Excel
• VLOOKUP and Return All Matches in Excel (7 Ways)
• Use VLOOKUP to Sum Multiple Rows in Excel (4 Ideal Examples)
• 10 Best Practices with VLOOKUP in Excel
• VLOOKUP with Two Lookup Values in Excel (3 Simple Methods)
• How to Use VLOOKUP for Multiple Columns in Excel (6 Examples)