Lookup and Return Values in an Entire Row/Column in Excel

VLOOKUP is one of the most used functions in Excel. It looks for a value in a range and returns a corresponding value in a specified column number.

Now I came across a problem where I had to lookup entire row and return the values in all the columns from that row (instead of returning a single value).

So here is what I had to do. In the below dataset, I had Sales Rep names and the Sales they made in 4 quarters in 2012. I had a drop down with their names, and I wanted to extract the maximum sales for that Sales Rep in those four quarters.

I could come up with 2 different ways to do this – Using INDEX or VLOOKUP.

Lookup Entire Row / Column Using INDEX Formula

Here is the formula I created to do this using Index

=LARGE(INDEX($B$4:$F$13,MATCH(H3,$B$4:$B$13,0),0),1)

How it works:

Let first look at the INDEX function that is wrapped inside the LARGE function.

=INDEX($C$4:$F$13,MATCH(H3,$B$4:$B$13,0),0)

Let’s closely analyze the arguments of the INDEX function:

• Array – $B$4:$F$1
• Row Number –  MATCH(H3,$B$4:$B$13,0)
• Column Number – 0

Notice that I have used column number as 0.

The trick here is that when you use column number as 0, it returns all the values in all the columns. So if I select John in the drop down, the index formula would return all the 4 sales values for John {91064,71690,67574,25427}.

Now I can use the Large function to extract the largest value

Pro Tip - Use Column/Row number as 0 in Index formula to return all the values in Columns/Rows.

Lookup Entire Row / Column Using VLOOKUP Formula

While Index formula is neat, clean and robust, VLOOKUP way is a bit complex. It also ends up making the function volatile. However, there is an amazing trick that I would share in this section. Here is the formula:

=LARGE(VLOOKUP(H3,B4:F13, ROW(INDIRECT("2:"&COUNTA($B$4:$F$4))), FALSE),1) 

How it works

• ROW(INDIRECT(“2:”&COUNTA($B$4:$F$4))) – This formula returns an array {2;3;4;5}. Note that since it uses INDIRECT, this makes this formula volatile.

• VLOOKUP(H3,B4:F13,ROW(INDIRECT(“2:”&COUNTA($B$4:$F$4))),FALSE) – Here is the best part. When you put these together, it becomes VLOOKUP(H3,B4:F13,{2;3;4;5},FALSE). Now notice that instead of a single column number, I have given it an array of column numbers. And VLOOKUP obediently looks up values in all these columns and returns an array.

• Now just use LARGE function to extract the largest value.

Remember to use Control + Shift + Enter to use this formula.

Pro Tip - In VLOOKUP, instead of using a single column number, if you use an array of column numbers, it will return an array of lookup values.

You may also like the following Excel tutorials:

• VLOOKUP Vs. INDEX/MATCH
• VLOOKUP vs XLOOKUP Function
• How to make VLOOKUP Case Sensitive.
• How to Use VLOOKUP with Multiple Criteria.
• How to Sum a Column in Excel
• How to Return Cell Address Instead of Value in Excel
• How to Apply Formula to the Entire Column in Excel