Moving Match formula

[Cheeky_Monkey]

I've tried really hard to find an answer to this problem but cannot find it.

Basically I have an table which is populated as below:

[TABLE="width: 429"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Band 1[/TD]
[TD]Band 2[/TD]
[TD]Band 3[/TD]
[TD]Band 4[/TD]
[TD]Band 5[/TD]
[/TR]
[TR]
[TD]Product Group A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]Product Group B[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Product Group C[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]45[/TD]
[/TR]
[TR]
[TD]Product Group D[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD]Product Group E[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[/TR]
</tbody>[/TABLE]

Now I want to find the Band of a particular product based upon its Qty. I have used Match(Criteria, Range, 1) to find the Band for a particular product group. However this works on one line. But my search line will vary depending on the Product group of the item. i could use multiple ifs but I have many more product groups.

What I need to do is identify the product group an item belongs to and then match on that line.

E.g

Item A belongs to Group C and 33 QTY sold = Band 2
Item C belongs to Group E and 33 QTY sold = Band 1

Thanks in advance

 

[Peter_SSs]

E.g

Item A belongs to Group C and 33 QTY sold = Band 2
Item C belongs to Group E and 33 QTY sold = Band 1

Welcome to the MrExcel board!

These example don't seem consistent to me.
For Group C, 33 comes between 30 in Band 2 and 35 in Band 3. You say the result should be Band 2 - the boundary on the lower side of 33.
For Group E, 33 comes below 50 in Band 1 but you say the result should be Band 1 - the boundary on the higher side of 33.


Can you explain further?

What I need to do is identify the product group an item belongs to ..

How are we going to do that as you haven't given us any information about it?

 

[Cheeky_Monkey]

Sorry for the confusion. The values are the upper qty of the quantity. So anything above that number goes to the next band. Hence I just noticed my error and in fact Item A should be in Band 3

 

[Marcelo Branco]

Maybe something like this...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD]

Band 1​

[/TD]
[TD]

Band 2​

[/TD]
[TD]

Band 3​

[/TD]
[TD]

Band 4​

[/TD]
[TD]

Band 5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

Item​

[/TD]
[TD="bgcolor: #D9D9D9"]

Group​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Product Group A​

[/TD]
[TD]

10​

[/TD]
[TD]

25​

[/TD]
[TD]

40​

[/TD]
[TD]

55​

[/TD]
[TD]

60​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

A​

[/TD]
[TD="bgcolor: #D9D9D9"]

Product Group C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Product Group B​

[/TD]
[TD]

15​

[/TD]
[TD]

20​

[/TD]
[TD]

25​

[/TD]
[TD]

35​

[/TD]
[TD]

40​

[/TD]
[TD][/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]

C​

[/TD]
[TD="bgcolor: #D9D9D9"]

Product Group E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Product Group C​

[/TD]
[TD]

25​

[/TD]
[TD]

30​

[/TD]
[TD]

35​

[/TD]
[TD]

40​

[/TD]
[TD]

45​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Product Group D​

[/TD]
[TD]

100​

[/TD]
[TD]

150​

[/TD]
[TD]

200​

[/TD]
[TD]

250​

[/TD]
[TD]

300​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Product Group E​

[/TD]
[TD]

50​

[/TD]
[TD]

60​

[/TD]
[TD]

70​

[/TD]
[TD]

80​

[/TD]
[TD]

90​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

Item​

[/TD]
[TD]

Qty​

[/TD]
[TD]

Result​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

A​

[/TD]
[TD]

33​

[/TD]
[TD]

Band 2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

C​

[/TD]
[TD]

33​

[/TD]
[TD]

Band 1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Create a table Item - Group (gray area in my example)

Formula in C9 copied down
=INDEX(B$1:F$1,IFERROR(MATCH(B9,INDEX(B$2:F$6,MATCH(VLOOKUP(A9,$I$2:$J$3,2,0),A$2:A$6,0),0)),1))

Hope this helps

M.

 

[Cheeky_Monkey]

Welcome to the MrExcel board!

How are we going to do that as you haven't given us any information about it?

For the purpose of this example I have already provided the information in the examples so no need to look at that element.

Item A is in Group C
Item C is in Group E.

My main focus to bring back the Band name or column reference based upon the qty sold of a given product. The row that the search needs to be completed will vary dependent on the product group. Happy for people to use own values for the relationship of item numbers and product groups


Apologies for not being concise. (My first post on this forum)

 

[Marcelo Branco]

Sorry for the confusion. The values are the upper qty of the quantity. So anything above that number goes to the next band. Hence I just noticed my error and in fact Item A should be in Band 3

This is a different requirement
What really do you want? Item A Band 2 or Band 3?

M.

 

[Cheeky_Monkey]

Thanks Marcelo. That is exactly what I was after.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

By the way, a more robust formula (it handles non-existent items)

C9 copied down
=IF(ISNUMBER(MATCH(A9,I:I,0)),INDEX(B$1:F$1,IFERROR(MATCH(B9,INDEX(B$2:F$6,MATCH(VLOOKUP(A9,$I$2:$J$3,2,0),A$2:A$6,0),0)),1)),"Item doesn't exist")

M.