Jun 01 2022 02:03 PM
Can you use a VLookup within a VLookup to fill the table_array value?
Jun 01 2022 02:36 PM
Jun 01 2022 03:18 PM
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! |
Jun 01 2022 03:50 PM - edited Jun 01 2022 03:51 PM
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.
Jun 02 2022 04:23 PM
Jun 03 2022 08:26 AM
Jun 03 2022 08:40 AM
Jun 03 2022 01:28 PM
Jun 03 2022 01:49 PM
@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)
Jun 03 2022 02:00 PM
Jun 03 2022 02:37 PM
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
Jun 03 2022 02:37 PM
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