SOLVED

VLookup nesting

Copper Contributor

Can you use a VLookup within a VLookup to fill the table_array value?

11 Replies
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) ).

@Patrick2788 

 

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.

AbbrTable_Array 1PP1PP2PP3PP4PP5
JBar$E$1:$I$13 20    
EBer$E$1:$I$13 305.75   
TKit$E$1:$I$13 4 20   
JLee$E$1:$I$13 50115000
CMil$E$1:$I$13 60115000
CSal$E$1:$I$13 72424242424
TSmo$E$1:$I$13 800000
BStr$E$1:$I$13 900000
   100115000
PP2  1100000
BStr  1200000
   130115000
#VALUE!        

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.
Choose 'Open full text editor' at the bottom of the compose box .
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.

@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.
best response confirmed by VI_Migration (Silver Contributor)
Solution

@Betsy1915 sorry I had a small typo:

=HLOOKUP($A$11,INDIRECT(VLOOKUP($A$12,$A$1:$I$13,2,FALSE)),4,FALSE)

basically that internal VLOOKUP range should be $A$1:...

see attached

I got it to work! Thank you so much!
1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

@Betsy1915 sorry I had a small typo:

=HLOOKUP($A$11,INDIRECT(VLOOKUP($A$12,$A$1:$I$13,2,FALSE)),4,FALSE)

basically that internal VLOOKUP range should be $A$1:...

see attached

View solution in original post