Index Match - wrong results

Fi_83

New Member
Joined
May 22, 2020
Messages
5
Office Version
365
Platform
Windows
Looking for help – my mistake must be something simple, but I cant figure out why my formula results are wrong, and need someone to run their eye over it please :)



I have created a summary sheet to show user grades from various courses completed.
Formula on CA column is =INDEX('HS 2020 Course'!$AE$6:$AE$10,MATCH($E6,'HS 2020 Course'!$C$6:$C$10))
example 1.PNG


The grades are on the separate sheet in same excel document. This one called "HS 2020 Course" I am using email address to match my data as the names are not always an reliable match.
example 2.PNG


Thanks!!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,786
Office Version
365
Platform
Windows
try
=INDEX('HS 2020 Course'!$AE$6:$AE$10,MATCH($E6,'HS 2020 Course'!$C$6:$C$10,0))

Otherwise you are looking for an approximate match.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,235
Office Version
365
Platform
Windows
Another option if you have XLOOKUP (it has exact match as its default)

=XLOOKUP(E6,'HS 2020 Course'!$C$6:$C$10,'HS 2020 Course'!$AE$6:$AE$10)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,562
Messages
5,487,578
Members
407,605
Latest member
PACULA

This Week's Hot Topics

Top