**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:

`=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 in 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**.

**Read More:** **How to VLOOKUP with Multiple Conditions in Excel (2 Methods)**

**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 **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:** **Excel VLOOKUP to Return Multiple Values Vertically**

**Similar Readings**

**VLOOKUP Not Working (8 Reasons & Solutions)****What Is a Table Array in VLOOKUP? (Explained with Examples)****Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)****How to Combine Excel SUMIF & VLOOKUP Across Multiple Sheets****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:

`=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, so now 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 on **Column B**.

**Read More:** **How to VLOOKUP from Multiple Columns with Only One Return in Excel (2 Ways)**

**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.

**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)****VLOOKUP and Return All Matches in Excel (7 Ways)****Use of VBA VLOOKUP to Find Values from Another Worksheet in Excel****VLOOKUP SUM Multiple Rows (4 Ways with Alternative)****10 Best Practices with VLOOKUP in Excel****VLOOKUP with Two Lookup Values in Excel (2 Approaches)****How to Use VLOOKUP for Multiple Columns in Excel**