Lookup 2 criteria on other sheet; return other cell value

[wilkisa]

I have 2 sheets in my workbook, Source and Target. On my Target sheet, I have a Vendor Number column and a Code column. I need to get the Cost information from the Source sheet.

In the Source sheet I have the Vendor Number column but the Code is a series of 16 columns. I need to have the Vendor Number match from Target to Source AND I need to have the Code matched to any one of the 16 Code columns. If I have a match on both items, I need the Cost value from column AF in the Source sheet placed into column F on the Target sheet.

Example--TARGET sheet:

Col. C-Vendor Number
101010
111222
222555

Col. D-Code
AB
LM
8F

Example--SOURCE sheet:

Col. C-Vendor Number
110011
111222
222555

Cols F G H I J K L M N O P Q R S T U-Code
AB BC LM 3D HF PQ 5K
3D 5K HF GR LM BC
AB 8F

There is no set number of the 16 Code columns used and no pattern for discovering where the matching code will be...if there is one at all.

Can someone help with this? Please?

Thanks,
Shirlene

 

[Domenic]

Assuming that Sheet2 contains the source data, and Sheet1 is the target sheet, try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

Sheet1!F2, copied down:

=INDEX(Sheet2!$AF$2:$AF$4,MATCH(1,IF(Sheet2!$C$2:$C$4=C2,IF(MMULT(--(Sheet2!$F$2:$U$4=D2),TRANSPOSE(COLUMN(Sheet2!$F$2:$U$4)^0)),1)),0))

Adjust the range accordingly.

Hope this helps!

 

[wilkisa]

Thank you for the help Domenic. I did my own tweaking and it is working great. I have no experience with the MMULT function so I will have to bone up on it.

Thanks again,
Shirlene