INDEX & MATCH Returns #N/A

Matt Butcher

New Member
Joined
Jun 30, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a workbook with two sheets. On the first sheet, called "Master", I have a lot of product data for items that we have sold, which includes a list of first names in column AH, surnames in column AI and a column AJ where I want to put in the contact person's email address.

On the second sheet, called "Email Lookup", I have an export of contact details from another database which I want to match to complete the email address field as above. This sheet contains six columns of data; column B has the surnames, column C has the first names and column F has the email address.

I have the following formula in "Master" sheet AJ2:

{=INDEX('Email Lookup'!$A$2:$F$8071,MATCH(1,('Email Lookup'!$B:$B=AI2)*('Email Lookup'!$C:$C=AH2),0),6)}

I have manually checked some entries against the lookup list and I know that I have matches.

All I get out is #N/A.

Thanks
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
Are you 100% sure they match? Try a simple =AH2='Email Lookup'!C?? change the ?? to a cell number you think matches, what do you get? Do the same with =AI2='Email Lookup'!B??
 

Matt Butcher

New Member
Joined
Jun 30, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
It turns out that all the first names on the "Email Lookup" sheet had a space in front of them. Now removed using the TRIM function and the formula works. Thank you!

The new issue is that the email address that it is returning is from the next row down. This is the same for three different examples. So for example when I look up the names Smith and John it returns FJones@btinternet.com

Col B Col C Col F
Smith John John.Smith@gmail.com
Jones Fred FJones@btinternet.com
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,992
Office Version
  1. 365
Platform
  1. Windows
The new issue is that the email address that it is returning is from the next row down.
Because the match ranges do not start on the same row as the index range. Unless you specifically want an offset to be used, all ranges should be equal.

=INDEX('Email Lookup'!$A$2:$F$8071,MATCH(1,('Email Lookup'!$B$2:$B$8071=AI2)*('Email Lookup'!$C$2:$C$8071=AH2),0),6)

Or a more efficient version,

=LOOKUP(2,('Email Lookup'!$B$2:$B$8071=AI2)/('Email Lookup'!$C$2:$C$8071=AH2),'Email Lookup'!$F$2:$F$8071)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,388
Messages
5,601,382
Members
414,448
Latest member
Jessica 22664

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top