Lookup 2 criteria on other sheet; return other cell value

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
651
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2007
Platform
  1. Windows
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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,719
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

Well-known Member
Joined
Apr 7, 2002
Messages
651
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2007
Platform
  1. Windows
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
 

Forum statistics

Threads
1,140,938
Messages
5,703,271
Members
421,289
Latest member
fbohlandt

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top