lookup value if multiple matches are met

G

Go Bucks!!!

Hi all,

I have two spreadsheets. Spreadsheet 1 contains an invoice # (A:A) and Part
# (B:B). Spreadsheet 2 Contains the same information invoice # (C:C) and Part
# (E:E).

On Spreadsheet 1,each invoice contains multiple partnumbers.
ColA ColB
89853656 FS485AW#ABA
89853656 AH058AA
89853657 RA373AA#ABA
89853694 KD911A4#ABA
89853694 U4414E
89853694 CC532A
89853694 KP080AA#ABA

On spreadsheet 2, if my serial number, 89853694, exists on Sheet 1 lookup my
part number, U4414E. If my part number, U4414E is found, return U4414E -
else return "".

Thanks,
 
L

Luke M

Selecting a cell in row 2 of Sheet2, input this formula:

=IF(SUMPRODUCT(--('Sheet1'!A2:A200=C2),--('Sheet1'!B2:B200=E2))>0,E2,"")

Adjust range sizes as necessary. Note that you can not callout entire
columns (A:A) in SUMPRODUCT, unless using XL 2007.
 
G

Go Bucks!!!

Thanks Luke - works great.

FYI I just found an alternate way while digging through postings ...

=INDEX($B$1:$B$100,MATCH(1,(Sheet1$A$1:$A$200=$C2)*(Sheet1$B$1:$B$200=$E2),0))


Both work. I prefer yours.

Thanks Luke for such a quick response.

-Go Bucks!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top