Forum Discussion
VLOOKUP using user input to determine column
Instead of snapshots, please attach your sample file next time so that the Community won''t painstakingly need to reproduce your data in a new Excel workbook, just as I did. In my earnest effort to furnish you with a probably acceptable solution, I hope that this formula in Sheet2!G4 in the attached file will deliver your expected result:
=VLOOKUP(F4,
Sheet3!A$5:I$81,
MATCH(C4,
--LEFT(Sheet3!B$4:I$4,2),1)+1,
0)
Note that the foregoing is an array formula, such that it must be confirmed with Ctrl+Shift+Enter.
Let me explain why it is an array formula. Neither the lookup_array argument of MATCH nor the col_index_num argument of VLOOKUP can process an array operation.
To convert to a non-array formula, the lookup_array argument of MATCH must be wrapped with INDEX like this formula in Sheet2!G5:
=VLOOKUP(F5,
Sheet3!A$5:I$81,
MATCH(C5,
INDEX(--LEFT(Sheet3!B$4:I$4,2),0),1)+1,
0)
Because the array argument of INDEX openly processes array operations, the foregoing formula is confirmed with a regular Enter.
Thank you for taking time to respond. My apologizes for not attaching the file( you will find it attached to this reply). I attempted to input your formulas into my sheet referencing your example, however for some reason it didn't work, most likely due to an error on my part. You will find my attempts to apply your formulas on the results sheet in the pushups score column. Further help would be greatly appreciated.
In the attached version of your file, the formula in G4 is:
=VLOOKUP(F4,Pushup!A$5:R$82,
MATCH(C4,INDEX(--LEFT(Pushup!B$4:I$4,2),
0),1)+1+9*(B4="F"),0)Note that the formulas in I4 and K4 are similar to the foregoing. Please confirm if such formulas return your expected results.
- This worked, thank you very much!
