Link to home
Start Free TrialLog in
Avatar of ArisaAnsar
ArisaAnsarFlag for United States of America

asked on

Excel Formula

Hi,
I have a spreadsheet.   On Sheet 2, is a list of email address (column A) and a specific product name (column C).  Against Sheet 1, I have to look to see if that email address (column A) and product code (column B) is also listed.

I cannot use a vlookup because that email address may be listed multiple times but different product codes on Sheet 1.  Do you have a suggestion on how I can do this?

Any help would be greatly appreciated.
Formula.xlsx
Avatar of Camy
Camy
Flag of United Kingdom of Great Britain and Northern Ireland image

You should be able to use INDEX and MATCH hopefully.
http://exceltactics.com/vlookup-multiple-criteria-using-index-match/
Formula.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Naresh Patel
Naresh Patel
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rob Henson
In sheet2 concatenate the e-mail with product number with simple formula:

=A1&B1

You can include a character in between if you so wish so that you can see the split, eg =A1&"-"&B1

Then on sheet1 use the following:

=IFERROR(MATCH($A1&$B1,Sheet2!$C:$C,0),"No Match")

This will return the row number where matched or "No match" when not matched.

Thanks,
Rob H
Avatar of ArisaAnsar

ASKER

Thanks.  This worked perfectly for me.