VLOOKUP using user input to determine column

Copper Contributor

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. 

 

Feel free to ask questions

Thank you in advance!

 

 

2.JPG2.1.JPG1.4.JPG

6 Replies

@jman9635 

For such sample model

image.png

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")

@jman9635 

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.

@Twifoo 

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. 

@jman9635 

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!
I’m glad to know that I’ve helped you. I painstakingly did!