Reference 2 values to populate a third (advanced Vlookup? DGET? Index/Match?)

Hi Guys,

So I have been looking for a few days for a solution to this - maybe you can help.

In sheet 2, I need to populate Buyer Number by referencing the two values (BU, Item#)that appear on Sheet 1 and Sheet 2.

The issue that I was having with an Index/Match was that it wasnt helping for multiple values.

For example, the same item number might exists at 5 different Business Units, each with its own Buyer Number. It might exists twice under the same BU and same Buyer.

The location field can be ignored.

Sheet2
Business Unit Item Number Location Buyer Number
1200 140229 QA
1020 151101 MR
1200 210329 MR
1020 219101 QA
1020 275101 QA
1600 168171003 QA
1020 168171003 MR
1000 168171003 MR
1200 168171003 QA
1300 168171003 QA
1020 168175001 MR
1000 168175001 QA
1000 168183001 MR
1600 168183001 MR
1000 168199001 MR
1000 M934840A003 MR
1200 M934840A003 QA
1000 M934843A001 QA
1000 M934845A020 QA
1300 M934845A020 MR
1300 M934881A002 QA
1300 M934900A001 MR
1300 M934901A001 QA
1300 M934902A001 QA
1300 M934903A001 QA
1300 M934903A002 QA
1000 M935123A001 QA

Sheet1
Business Unit Item Number Buyer Number
1600 168171003 929114
1020 168171003 9048811
1000 168171003 903091
1200 168171003 9096328
1300 168171003 9039276
1020 168175001 9048811
1000 168175001 909394
1000 168183001 978412
1600 168183001 929114
1000 168199001 909394
1000 M934840A003 904415
1200 M934840A003 954831
1000 M934843A001 9048811
1000 M934845A020 905810
1300 M934845A020 9097971
1300 M934881A002 9091989
1300 M934900A001 9039276
1300 M934901A001 9039276
1300 M934902A001 9039276
1300 M934903A001 9039276
1300 M934903A002 9039276
1000 M935123A001 9090771
1200 140229 9096328
1020 151101 905810
1200 210329 9098959
1020 219101 9091065
1020 275101 9091065



Any ideas?

Thanks.

Also, it would be great if this could deal with a few blank values in buyer number

this seems to be....awesome.

I didnt think Sumproduct would work when the item # field has letters in it.

what does "--" do?

Thanks!

Lots of info on sumproduct and unary (--) operator at http://www.xldynamic.com/source/xld....T.html#classic

NBVC,

When I translated this to my much larger sheet, the Buyer Number remained 0 for Item Numbers that had letters in them

In the small example I gave with you answer, the letters did not have an impact.

Any ideas?

Thanks,

Ted

i did stick $ signs in, that is all