# Moving Match formula

#### Cheeky_Monkey

##### New Member
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 1 Band 2 Band 3 Band 4 Band 5 Product Group A 10 25 40 55 60 Product Group B 15 20 25 35 40 Product Group C 25 30 35 40 45 Product Group D 100 150 200 250 300 Product Group E 50 60 70 80 90

<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

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

Last edited:

#### Cheeky_Monkey

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

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

##### New Member
Thanks Marcelo. That is exactly what I was after.

#### Marcelo Branco

##### MrExcel MVP
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.

Replies
1
Views
38
Replies
2
Views
85
Replies
20
Views
423
Replies
1
Views
355
Replies
9
Views
201

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.

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