# 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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### Domenic

##### MrExcel MVP
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!

#### wilkisa

##### Well-known Member
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
2
Views
198
Replies
0
Views
144
Replies
1
Views
195
Replies
2
Views
383
Replies
0
Views
429

### Forum statistics

1,181,322
Messages
5,929,289
Members
436,660
Latest member
Mouseinalabyrinth

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

### Which adblocker are you using?

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