Lookup 2 criteria on other sheet; return other cell value

wilkisa

Well-known Member
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

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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))

Hope this helps!

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

Replies
3
Views
92
Replies
18
Views
762
Replies
1
Views
258
Replies
8
Views
344
Replies
8
Views
462

1,221,423
Messages
6,159,822
Members
451,591
Latest member
j0eyjedi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back