lookup two column and match

[vani2004]

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>

 

[cyrilbrd]

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.

 

[vani2004]

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.

 

[cyrilbrd]

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)

 

[vani2004]

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.
I appreciate your help.

 

[cyrilbrd]

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.
I appreciate your help.

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.00
010234	50-35400	3.00

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

 

[cyrilbrd]

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?

 

[vani2004]

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

 

[cyrilbrd]

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.