Oct 11 2019 02:56 PM
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!
Oct 11 2019 03:29 PM
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")
Oct 12 2019 10:51 AM
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.
Oct 14 2019 12:36 PM
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.
Oct 14 2019 11:30 PM
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.
Oct 15 2019 11:46 AM
Oct 15 2019 12:45 PM