LinkBack URL
About LinkBacks
I have a table showing products and pricing bands, based on quantity ordered (sample attached)
The products are in rows with pricing bands in columns.
If I know the product and the order quantity, how do I find out which band it is in?
I have a table showing products and pricing bands, based on quantity ordered (sample attached)
The products are in rows with pricing bands in columns.
If I know the product and the order quantity, how do I find out which band it is in?
Excel is a constant learning process and it's great to help each other. If any of us have helped you today, a click on the "reputation" star on the left is appreciated.
what would make Prod A with a value of 25 end up in Band 3? It would look like it should either be Band 1 or Band 2 depending on if those numbers are minimum or maximum.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Oops! Apologies - I'd been playing around with the numbers!
Prod A with a value of 25 should be in Band 1.
Last edited by shirleyxls; 10-23-2019 at 04:12 PM.
how many products and bands will you have in your actual data?
10 bands and about 10 products
They're actually product categories so not likely to grow to a large number.
The quantity band is based on an order quantity UP to (and including) the value shown in that band
If the quantity is greater than the last band, they will just get the last band value.
So for Product A, an order of 25 would be Band 1, 55 would be Band 2, 90 would be Band 4
Hope that helps - this one has got me stumped!
Last edited by shirleyxls; 10-23-2019 at 04:12 PM.
I did it this way...
=IFERROR(LOOKUP(2,1/(B4=C9)/(C4:F4<=C10),C3:F3),IFERROR(LOOKUP(2,1/(B5=C9)/(C5:F5<=C10),C3:F3),LOOKUP(2,1/(B6=C9)/(C6:F6<=C10),C3:F3)))
and I know it can be expanded to cover 10 bands (that is easiest by changing C3:F3 to C3:L3) and increasing the number of lookups to 10 but I know there is an easier way.
I'll just have to keep playing with it. Someone will probably come along with a simpler version.
Thanks Sambo kid. Glad it's not just me that's struggling with this!
I wanted to make it as dynamic as possible so that if someone adds a new product category we don't have to further expand the formula.
Appreciate the time spent - let me know if anything else springs to mind :-)
Still playing with this but came up with a possible solution using INDIRECT
Formula:
=INDEX(C3:F3,MATCH(C10,INDIRECT("C"&MATCH(C9,B1:B6,0)&":F"&MATCH(C9,B1:B6,0)),1))
I might need to play with the ranges a bit but it seems to work (unless the quantity is less than the lowest value in Band 1, but I'll put an IFERROR in to capture that once I've tested it a bit more)
Open to more elegant solutions if anyone has any!
Using your original file:
G9: =MATCH(C9,B4:B6,0)
G10: =MATCH(C10,INDEX(C4:F6,G9,0))
G11: =INDEX(C3:F3,G10)
Text in E10 should read:
Column on this row is
You can substitute the individual parts into the formula if you are looking for one composite formula.
Hope this helps.
Pete
Many thanks Pete! "The column on this row" was the bit which I'd got wrong
I've now built this into a single formula
Formula:=INDEX(C3:F3,MATCH(C10,INDEX(C4:F6,MATCH(C9,B4:B6,0),0)))
It works perfectly!
Thanks again for the solution - much appreciated
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Thanks for the rep, Shirley. The composite formula is certainly shorter than the others that have been mentioned.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks