# 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: FREE EXCEL BOOK

## Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

### 1 thought on “Lookup and Return Values in an Entire Row/Column in Excel”

1. Keep me posted on videos updates on LOOKUP post examples on YouTube and reminder to
My email id