INDEX & MATCH Returns #N/A

[Matt Butcher]

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

 

[gaz_chops]

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]

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]

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]

Updated and now all works perfectly. Thank you!