 • 540K Members
• 3,141 Online
• 644K Conversations

Highlighted

# VLOOKUP using user input to determine column

Step1: User inputs data

Step 2: User input is transferred to 2nd table

Step 3: Score column references 2nd sheet(Pushup) table to determine score. Uses reps to determine row and age for the column.

Issue: Having trouble with formula to reference 2nd sheet and find score attached to the user inputted age and reps.   6 Replies
Highlighted

# Re: VLOOKUP using user input to determine column

For such sample model formula in H3 could be

``````=IFNA(
INDEX(
\$B\$5:\$D\$46,
MATCH(G3,\$A\$5:\$A\$46,0),
MATCH(1,INDEX((F3>=--LEFT(\$B\$4:\$D\$4,2))*(F3<=--RIGHT(\$B\$4:\$D\$4,2)),0),0)
),
"no such")``````
Highlighted

# Re: 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.

Highlighted

# Re: VLOOKUP using user input to determine column

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.

Highlighted

# Re: VLOOKUP using user input to determine column

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.

Highlighted

# Re: VLOOKUP using user input to determine column

This worked, thank you very much!
Highlighted

# Re: VLOOKUP using user input to determine column

I’m glad to know that I’ve helped you. I painstakingly did!