Lookup 2 criteria on other sheet; return other cell value

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
649
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Domenic

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

Watch MrExcel Video

Forum statistics

Threads
1,113,848
Messages
5,544,640
Members
410,627
Latest member
georgealice
Top