Moving Match formula

Cheeky_Monkey

New Member
Joined
Aug 9, 2019
Messages
4
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:

Band 1Band 2Band 3Band 4Band 5
Product Group A1025405560
Product Group B1520253540
Product Group C2530354045
Product Group D100150200250300
Product Group E5060708090

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

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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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.
:confused:

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?
 
Last edited:

Cheeky_Monkey

New Member
Joined
Aug 9, 2019
Messages
4
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,501
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Maybe something like this...


A
B
C
D
E
F
G
H
I
J
1
Band 1​
Band 2​
Band 3​
Band 4​
Band 5​
Item​
Group​
2
Product Group A​
10​
25​
40​
55​
60​
A​
Product Group C​
3
Product Group B​
15​
20​
25​
35​
40​
C​
Product Group E​
4
Product Group C​
25​
30​
35​
40​
45​
5
Product Group D​
100​
150​
200​
250​
300​
6
Product Group E​
50​
60​
70​
80​
90​
7
8
Item​
Qty​
Result​
9
A​
33​
Band 2​
10
C​
33​
Band 1​
11

<tbody>
</tbody>


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.
 
Last edited:

Cheeky_Monkey

New Member
Joined
Aug 9, 2019
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,501
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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 :confused:
What really do you want? Item A Band 2 or Band 3?

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,501
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,719
Messages
5,626,469
Members
416,187
Latest member
L_D18

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top