How to use Match formula

[Stella2012]

1st example
I try to record the grade based on score using Choose and Match formula. Yet it didn't work as I expected.
For score>90, the grade is A.

2nd example
5 people made a guess. The correct answer is 3. The winner should be B. I used Index & Match formula. It didn't work, either.

Could anyone help with it?


excel match.xlsx

[NBVC]

IN F1, try:

=IFERROR(INDEX(Table1[Grade],MATCH(TRUE,INDEX(Table1[Score]<=E2,0),0)),"F")

copied down

in K3, try:

=INDEX(H3:H7, MATCH(MIN(INDEX(ABS(I3:I7-K2),0)), INDEX(ABS(I3:I7-K2),0),0))

[Stella2012]

Thanks for your reply. Yet it didn't work either

[NBVC]

Both formulas?

The second formula was based on what it looked like you were trying to do, and that was to find the value that had the minimum different between the value and K2?

What exactly determines that B should be the answer, and not C?

[Lancewr]

Try reversing your lookup list (see attached)

[Stella2012]

Thanks.
I replied yesterday, yet it didn't display.
The 1st one works well. For the 2nd one, I made a mistake. The winner should be C.

IN F1, try:

=IFERROR(INDEX(Table1[Grade],MATCH(TRUE,INDEX(Table1[Score]<=E2,0),0)),"F")

copied down

in K3, try:

=INDEX(H3:H7, MATCH(MIN(INDEX(ABS(I3:I7-K2),0)), INDEX(ABS(I3:I7-K2),0),0))

---------- Post added at 11:10 AM ---------- Previous post was at 11:06 AM ----------

Thank you!

Try reversing your lookup list (see attached)