lookup value if multiple matches are met

[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,

 

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

 

[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!!!