Forum Discussion
VLookup nesting
Why doesn't this work? The Formula in the #Value! cell is =VLOOKUP($A$11,VLOOKUP($A$12,$E$1:$I$13,4,False)4,False). I am trying to return $E$1:$I$13 for the table_array value in the second VLOOKUP. Obviously, I'll have different arrays under Table Array but this is just a portion of the spreadsheet. Columns A and B will be on one worksheet and the columns with PP are on a different worksheet.
| Abbr | Table_Array | 1 | PP1 | PP2 | PP3 | PP4 | PP5 | |
| JBar | $E$1:$I$13 | 2 | 0 | |||||
| EBer | $E$1:$I$13 | 3 | 0 | 5.75 | ||||
| TKit | $E$1:$I$13 | 4 | 20 | |||||
| JLee | $E$1:$I$13 | 5 | 0 | 115 | 0 | 0 | 0 | |
| CMil | $E$1:$I$13 | 6 | 0 | 115 | 0 | 0 | 0 | |
| CSal | $E$1:$I$13 | 7 | 24 | 24 | 24 | 24 | 24 | |
| TSmo | $E$1:$I$13 | 8 | 0 | 0 | 0 | 0 | 0 | |
| BStr | $E$1:$I$13 | 9 | 0 | 0 | 0 | 0 | 0 | |
| 10 | 0 | 115 | 0 | 0 | 0 | |||
| PP2 | 11 | 0 | 0 | 0 | 0 | 0 | ||
| BStr | 12 | 0 | 0 | 0 | 0 | 0 | ||
| 13 | 0 | 115 | 0 | 0 | 0 | |||
| #VALUE! |
a) Table lookup meaning you want to do a lookup horizontal and vertical so the inside should return a whole column and then the outside would select from that column, or
b) do a look up that returns a cell with a "range reference" where the outside lookup should look
IF a) you need to use something more like an INDEX-MATCH on the inside to return the columns you need
IF b) I don't recommend this way but you would need to use INDIRECT to convert that cell VALUE (the cell values under "Table_Array" are VALUES NOT range references) into a range reference.
To further help please clarify what you are trying to do and please also let us know what version of Excel you are using (i.e. are you using Excel 365) because some of the newer function may be very helpful here.
- I am trying to do b), return a cell with a "range reference" where the outside lookup should look. I am using 365. I'll try indirect - I've never used that before.
Betsy1915 so that makes a little more sense but the example you give still has issues:
=VLOOKUP($A$11,VLOOKUP($A$12,$E$1:$I$13,4,False)4,False)
in this case I believe the "inside" VLOOKUP should really be:
INDIRECT(VLOOKUP($A$12,$E$1:$I$13,2,False))
because you want to lookup the value in column A and return the value from the 2nd column (B) in that range
but the outer VLOOKUP maybe you mean to be HLOOKUP? Because the value you are looking for is in the HEADER/1st ROW of that corresponding range and then you wanted to return the 4th row? from the corresponding found column?
so maybe:
=HLOOKUP($A$11,INDIRECT(VLOOKUP($A$12,$E$1:$I$13,2,False)),4,False)
- You are correct that I needed HLOOKUP and I had it in my original worksheet. However, when I try the string you provided, I get a #N/A error.