# lookup two column and match

#### vani2004

##### New Member
I have two worksheets (Sheet1 & Sheet 2) in my file

Sheet 1 has three column (ID, Dept, and rate)
Sheet 2 has three column (ID, Group #, and rate)
so 3 columns on each sheet (Column A, B & C)

On sheet 2, First i want to match "ID on both sheet"
Second, I want to match DEPT. in sheet 1 to match GROUP # on sheet 2
Answer would be the rate. Now that seems simple but here is the trick: 1 dept. number are different than Group #.

For example
Dept. "05" Equals Group # "50-35100" & "50-35400"

Dept 08 = 50-34200; 50-34400 (Group ID#)
010 = 50-33100
012 = 50-34100,50-34300;50-36100 and 50-36200
016 = 50-35200; 50-35300

1 dept. equals 2 or more group #.
Hence, Formula need to total up group # rates to equal one dept. ALSO, It NEED TO MATCH PRODUCT ID.

for example: dept 05 rate is \$10
Where as Dept 05 equal Group 50-35100 & 50-35400 where 50-35100 = \$4 & 50-35400 \$6
so dept. 05 total matches with group \$10 =\$10

Data from Sheet 1 Below
 Product ID dept. rate 10234 005 4.95 10234 008 6.89 10234 010 86.60 10234 012 2.52 10234 016 6.67 10309 005 5.01 10309 008 3.89 10309 010 94.73 10309 012 13.47 10309 016 6.67 10310 005 4.81 10310 008 4.29 10310 010 100.73 10310 012 8.93 10310 016 6.67 10359 005 5.01 10359 008 3.85 10359 010 111.33 10359 012 11.95 10359 016 6.67

<tbody>
</tbody>

Data from Sheet 2 below

 Product ID Group ID Rate 010234 50-34200 5.00 010234 50-35200 7.00 010234 50-35100 2.00 010234 50-35400 3.00 010234 50-33100 12.00 010234 50-33100 5.00 010234 50-33100 11.00 010234 50-33100 4.00 010234 50-33100 40.00 010234 50-33100 10.00 010234 50-33100 4.00 010234 50-34100 0.75 010234 50-34100 0.75 010234 50-34100 0.48 010234 50-34100 0.53 010234 50-34400 0.00 010234 50-34300 0.00

<tbody>
</tbody>

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi,
Product ID and Rate are the two fields found in both tables.
However in your example you use dept 05 and make it equal to group ID 50-35100 and 50-35400 could you please explain based on your sample as I fail to see the correlation. Thanks.

Hi,
Product ID and Rate are the two fields found in both tables.
However in your example you use dept 05 and make it equal to group ID 50-35100 and 50-35400 could you please explain based on your sample as I fail to see the correlation. Thanks.

Sure and thanks for asking for clarification;
We are restructuring and implementing different software/system. Old system/software used to have department number and new system has group id numbers. I am trying to compare rates between old and new system.
In Old sytem we had 5 different departments 05, 08, 10, 12, 16.
In new system: we have 11 different groups/Dept.

so we are splitting departments into group with new system.
 OLD NEW Dept. Equal GROUP ID 05 = 50-35100, 05-35400 08 = 50-34200, 50-34400 010 = 50-33100 012 = 50-34100,50-34300,50-36100,50-36200 016 = 50-35200, 50-35300

<tbody>
</tbody>

So in old system, "depend on Product ID" each department has set Rate. Now under new system, depend on product ID, that department rate split into different group id.
I am just trying to compare rate change between old system and new system.

hope this helpful and thank you looking into this. Let me know i can email you file.

Would this be correct?
 Product ID Group ID Rate old rate 10234 50-34200 5 6.89 10234 50-35200 7 6.67 10234 50-35100 2 4.95 10234 50-35400 3 4.95 10234 50-33100 12 86.6 10234 50-33100 5 86.6 10234 50-33100 11 86.6 10234 50-33100 4 86.6 10234 50-33100 40 86.6 10234 50-33100 10 86.6 10234 50-33100 4 86.6 10234 50-34100 0.75 2.52 10234 50-34100 0.75 2.52 10234 50-34100 0.48 2.52 10234 50-34100 0.53 2.52 10234 50-34400 0 6.89 10234 50-34300 0 2.52

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

Note that we have 7 occurrences of group-ID 50-33100 (old Dpt 10) and 5 occurrences of group-ID 50-34100 (old Dpt 12)

Yes, you are on right track Cyrilbrd. Only thing is you have formula on wrong worksheet. I want my formula on worksheet Sheet1.

 Product ID dept. rate Formula (New rate) 10234 005 4.95 5.00 10234 008 6.89 5.00 10234 010 86.60 86.00 10234 012 2.52 2.52 10234 016 6.67 7.00

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

So i can see big difference in dept. 08. Other departments are off but they may be because of rounding.

Yes, you are on right track Cyrilbrd. Only thing is you have formula on wrong worksheet. I want my formula on worksheet Sheet1.

 Product ID dept. rate Formula (New rate) 10234 005 4.95 5.00 10234 008 6.89 5.00 10234 010 86.60 86.00 10234 012 2.52 2.52 10234 016 6.67 7.00

<tbody>
</tbody>

So i can see big difference in dept. 08. Other departments are off but they may be because of rounding.

Understood, however I think we need to clarify data, based on your sample the rate of dept 005 is 5 but based on the data in post#3 the group ID for dept 005 are 50-35100 and 05-35400 and based on post#1 the rate is as follows:
 010234 50-35100 2 010234 50-35400 3

<tbody>
</tbody>
Therefore how did you get 5, did you sum the two rates?
The next is dept 008, with again two entries, one is rate 5 and the other one is rate 0, I assumed you summed them up to get a rate of 5.
But for dept 10 it does not follow the same logic, dept 10 is group 50-33100 and we have (7) seven different rates here:
 10 10234 50-33100 12 10 10234 50-33100 5 10 10234 50-33100 11 10 10234 50-33100 4 10 10234 50-33100 40 10 10234 50-33100 10 10 10234 50-33100 4

<colgroup><col width="65" span="4" style="width: 65pt;"></colgroup><tbody>
</tbody>
Which one should we use, as obviously it is not a sum.
Here is a summary of the number of rate per dept as per post#1.
 Count of Rate Dept Total 5 2 8 2 10 7 12 5 16 1 Grand Total 17

<colgroup><col><col></colgroup><tbody>
</tbody>

And here is the recap of those 17 values per dept.

 Dept rates 5 2 3 8 0 5 10 4 4 5 10 11 12 40 12 0 0.48 0.53 0.75 0.75 16 7

<colgroup><col><col><col span="6"></colgroup><tbody>
</tbody>
Thanks to clarify as I need to understand the underlying process.

Ok try like this:
given in Sheet2:
 Product ID Group ID Rate dept old_dept new_GID 10234 50-34200 5 8 5 50-35100 10234 50-35200 7 16 5 50-35400 10234 50-35100 2 5 8 50-34200 10234 50-35400 3 5 8 50-34400 10234 50-33100 12 10 10 50-33100 10234 50-33100 5 10 12 50-34100 10234 50-33100 11 10 12 50-34300 10234 50-33100 4 10 12 50-36100 10234 50-33100 40 10 12 50-36200 10234 50-33100 10 10 16 50-35200 10234 50-33100 4 10 16 50-35300 10234 50-34100 0.75 12 10234 50-34100 0.75 12 10234 50-34100 0.48 12 10234 50-34100 0.53 12 10234 50-34400 0 8 10234 50-34300 0 12

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col width="65" span="7" style="width:65pt"></colgroup><tbody><!--EndFragment--></tbody>

named ranges as follows:
Old_dept for range F2:F12
New_GID for range G2:G12

formula in D2 is =INDEX(Old_dept,MATCH(B2,New_GID,0)) copied down till needed.

In Sheet1:
 Product ID dept. old rate new rate 10234 5 4.95 5 10234 8 6.89 5 10234 10 86.6 86 10234 12 2.52 2.51 10234 16 6.67 7 10309 5 5.01 0 10309 8 3.89 0

<!--StartFragment--> <colgroup><col width="65" span="4" style="width:65pt"> </colgroup><tbody>
<!--EndFragment--></tbody>

Formula in D2 is =SUMIFS(Sheet2!\$C\$2:\$C\$18,Sheet2!\$A\$2:\$A\$18,"="&A2,Sheet2!\$D\$2:\$D\$18,"="&B2) copied down till needed.

Would that work for you?

OMG... I thought no one will be able to solve this because of it's complexity and hard to explain. It's working and exactly how i expected. I am out of words...i am so thankful for this. Yeaaaaa !
Thank you Mr. Genius!!

OMG... I thought no one will be able to solve this because of it's complexity and hard to explain. It's working and exactly how i expected. I am out of words...i am so thankful for this. Yeaaaaa !
Thank you Mr. Genius!!

Most welcome Vani2004, glad it worked for you.
Thanks for the kinds words and thanks for the feedback.

Replies
7
Views
476
Replies
3
Views
94
Replies
4
Views
274
Replies
0
Views
2K
Replies
4
Views
1K

1,219,574
Messages
6,149,079
Members
450,855
Latest member
onecodevee01

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