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.

Lookup Entire Row / Column

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:

Excel Ebook Subscribe

FREE EXCEL BOOK

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

Picture of Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

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

Leave a Comment

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

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

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

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

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

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