Index match multiple criteria

[projectile]

I have a multiple criteria lookup to return email address in Sheet1!$H:$H

First i need to match company number , e.g. E6 = 00001 against column Sheet1!$B:$B

if that is true, then match Contact name,

e.g. T6 = John against column Sheet1!$G:$G

if that is also true, then return email address from Sheet1!$H:$H



=INDEX(Sheet1!$H:$H,MATCH(1,(Sheet1!$B:$B=E6)*(Sheet1!$G:$G=T6),0))

I tried the above , but it complains about #NUM!

Please help

 

[barry houdini]

Are you using Excel 2003 or earlier? Try using a specific range for each column rather than the whole column, e.g.

=INDEX(Sheet1!$H$1:$H$1000,MATCH(1,(Sheet1!$B$1:$B$1000=E6)*(Sheet1!$G$1:$G$1000=T6),0))

confirm with CTRL+SHIFT+ENTER

 

[Roger Govier]

Hi

Try
=INDEX(Sheet1!$H:$H,(Sheet1!$B:$B=E6)*(Sheet1!$G:$G=T6)*ROW())

 

[projectile]

Hi, I tried this , but it is returning the wrong email address.

example

Company = 000167 , name = Emma

it is returning email address for someone with different email and company

Try
=INDEX(Sheet1!$H:$H,(Sheet1!$B:$B=E6)*(Sheet1!$G:$G=T6)*ROW())

 

[projectile]

Tried this

=INDEX(Sheet1!$H$1:$H$17822,MATCH(1,(Sheet1!$B$1:$B$17822=E6)*(Sheet1!$G$1:$G$17822=T6),0))

and worked.

Thanks

Are you using Excel 2003 or earlier? Try using a specific range for each column rather than the whole column, e.g.

=INDEX(Sheet1!$H$1:$H$1000,MATCH(1,(Sheet1!$B$1:$B$1000=E6)*(Sheet1!$G$1:$G$1000=T6),0))

confirm with CTRL+SHIFT+ENTER

 

[Roger Govier]

Hi, I tried this , but it is returning the wrong email address.

example

Company = 000167 , name = Emma

it is returning email address for someone with different email and company

Hi

Worked fine for me.
Could you let me see a sample of your data?