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
If the buyer numbers are indeed numbers and are unique, then sumproduct may do it.
e.g.
=Sumproduct(--(Sheet1!$A$2:$A$100=A2),--(Sheet1!$B$2:$B$100=B2),Sheet1!$C$2:$C$100)
where Sheet1!A1:C100 contain your database and you want to match to A2 and B2 of current sheet.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
this seems to be....awesome.
I didnt think Sumproduct would work when the item # field has letters in it.
what does "--" do?
Thanks!
Its called a double unary... it simply coerces TRUE/FALSE results in each conditional argument to 1s and 0s, respectively, so that the math can be done.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Lots of info on sumproduct and unary (--) operator at http://www.xldynamic.com/source/xld....T.html#classic
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
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
With the Sumproduct formula, only that last argument needs to be numeric..
i.e. the column that brings back the results. (in the sample case, column C).
If that column (The Business Number) has letters and numbers, then we cannot use Sumproduct... instead you will need something more complex..
e.g.
=INDEX(Sheet1!$C$2:$C$100,MATCH(1,INDEX((Sheet1!$A$2:$A$100=A2)*(Sheet1!$B$2:$B$100=B2),0),0))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks