How to check which price range a specific price falls into

[ExcelAtEverything]

Wondering what the best approach would be to do something. Below (Sheet1) is a list of products, with their respective product types, as well as prices for those products. In column E, I need to show which price group the product's price falls into.

The price groups have been pasted into another sheet (Sheet2), and are also shown below. I figured I would need to separate price ranges so that the min and max range are in separate cells, but before I did anything I wanted to ask here as to what the best approach would be. The list of products is quite lengthy (maybe 20k rows total). I'd be happy to provide any additional info if necessary. Thanks.

 

[Crystalyzer]

In sheet 2 do the groups have names? what are you looking to return to sheet 1?

 

[ExcelAtEverything]

In sheet 2 do the groups have names? what are you looking to return to sheet 1?

Hi Crystalyzer! No group names. I haven't done anything like that yet.
What I'm looking to return to Sheet1 is this: In column E (Price Group), I'd like to display which price group each product is in based on the product's price in column D, & which Category the product is in from column C.

So Sheet1 E3 would show $34-39 (Sheet2 G9).
Sheet1 E4 would show $15-20 (Sheet2 D4).

 

[Alex Blakenburg]

Based on you current banding image something like this might work, especially assuming you are looking at a Power Query solution.

Book2
	A	B	C	D	E
1	Product Type	Below	Above	Band Increment
2	Gloves	15	50	5
3	Glasses	6	41	5
4
Sheet1

 

[ExcelAtEverything]

Hi Alex, and thanks for your reply. Could you explain just a little further? While I assumed I would need to create a table like that as part of the solution, I don't quite understanding how to implement it to get to the solution. I just assumed that PQ would be the easiest way to get there. Not asking for you to write the code or give me the specific formulas or anything. Just a few pointers/steps as to how to make it work. Would I build that table out to include all price groups, then load into PQ, then create some sort of conditional column which looks thru all the groupings to find which one applies? Thanks again.

 

[ExcelAtEverything]

Additionally, the band increment that you show won't always be constant. Sometimes the increment could vary throughout the same row.

 

[Alex Blakenburg]

Additionally, the band increment that you show won't always be constant. Sometimes the increment could vary throughout the same row.

How did I know you were going to say that ? Just thought I could save you some maintenance.
However that does lead to the next thought.

Typically you do that sort of banding at the at the Pivot Table Level using consistent bands across all products so you can summarise the data and optionally show a chart.
If that is what you are aiming for you might want to work backwards from your end report or chart, to determine whether you current banding will achieve what you are aiming for.
For reporting purposes variable banding doesn't make sense, there will be little meaningful summarisation.
The alternative of looking at the report or chart by individual product type group would seem impractical.

The other thought was that you want some sort of volume pricing list. In which case you will need to set up your pricing table vertically anyway since you will need

• Product Type,
• From Qty,
• To Qty,
• Discount %.

At the Product Type Level, price wouldn't make sense so I am guessing discount %.

Let us know in what direction you are thinking.

I thought the 2nd technique in Mynda's (actually Phil's) approximate match lookup might be able to be adapted to your scenario but I would need to do more research.
(In the video you want from the 4 min 30 sec mark, runs to 10 mins)
https://www.myonlinetraininghub.com/power-query-approximate-match-vlookup
Others on the Forum I am sure will have done it before.

 

[ExcelAtEverything]

Hi, thanks Alex! Thanks so much but I ended it working through it, and they way I ended up doing it was via a helper table & some old-school regular formulas. Worked really well for what I needed. It was basically a product subcategory valuation tool, where I just needed to summarize the pricing subcategories.

 

[Alex Blakenburg]

Appreciate the update. Glad you were able get it working in a way you were happy with.

 

[ExcelAtEverything]

Thank you Alex.