# INDEX & MATCH Returns #N/A

#### Matt Butcher

##### New Member
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

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### gaz_chops

##### Well-known Member
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
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
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)

#### Matt Butcher

##### New Member
Updated and now all works perfectly. Thank you!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,376
Messages
5,836,906
Members
430,461
Latest member
flavgf

### 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.

### Which adblocker are you using?

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

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