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 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
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:
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. You may also like the following Excel tutorials:
VLOOKUP Vs. INDEX/MATCH 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
