Forum Discussion
Solved
VLookup nesting
Can you use a VLookup within a VLookup to fill the table_array value?
Yes, it's do-able. The VLOOKUP inside the table_array would have to spill to give you a vertical array (e.g. you'd lookup multiple values: =VLOOKUP(F1:F4,A1:B5,2,0) ).
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! - although yes it is possible to use VLOOKUP inside another VLOOKUP it is NOT what you want (I believe). It looks like you are either trying to do a
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.
Are you able to upload a sample workbook? It looks like INDEX-MATCH or VLOOKUP-MATCH would be the way to go but the sample above is limited because all is on one sheet. It looks like you can find the column by matching PP2 but it's not clear where BStr would be on the second sheet.
- Yes, I have a sample workbook but can't see how to upload it with my message.
