Excel Help with Upper and Lower Bounds

Hi there, I posted this question a few days ago and got a solution, but I think the solution is work backwards? If that makes sense... I need to return the price tier the item falls into based off current price and two bucked, but looks like its reversing. Does that makes sense? Let me know if it needs more clarification.... Can't figure out where I'm going wrong... Thank you!
excel-help-to-send.xlsm
Andrea BernardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Please give an example of what you think is wrong.
0
Andrea BernardAuthor Commented:
For example, the class number on the first tab of 101 and a price of $284.99, according to the matrix on the second tab, an item in class 101 and between 150-500 should fall in the High price tier bucket, but the formula says it's "Low". Does that make sense? Hope that helps! Thank you!!!
0
Martin LissOlder than dirtCommented:
Change the formula in C5 to

=INDEX(MATRIX!$B$3:$B$142,SUMPRODUCT((MATRIX!$A$3:$A$142=VALUE(B5))*(MATRIX!$C$3:$C$142>D5)*(MATRIX!$D$3:$D$142<D5)*ROW(MATRIX!$B$3:$B$142)))

and copy down.

By the way, I would suggest formatting columns "C" and "D" on the Matrix sheet so that the numbers display two decimal points because as it is it appears that for example 150 is both the upper bound of "medium" and the lower bound of "high", when in fact the upper bound of medium is 149.99.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wayne Taylor (webtubbs)Commented:
You need to start the Array range of the INDEX function from 1, or adjust the return from the SUMPRODUCT function according to the INDEX start row. See the below bolded text from each function.

=INDEX(MATRIX!$B$1:$B$142,SUMPRODUCT((MATRIX!$A$3:$A$142=VALUE(B5))*(MATRIX!$C$3:$C$142<D5)*(MATRIX!$D$3:$D$142>D5)*ROW(MATRIX!$B$3:$B$142)))

...or...

=INDEX(MATRIX!$B$3:$B$142,SUMPRODUCT((MATRIX!$A$3:$A$142=VALUE(B5))*(MATRIX!$C$3:$C$142<D5)*(MATRIX!$D$3:$D$142>D5)*ROW(MATRIX!$B$3:$B$142))-2)

The reason for that is the SUMPRODUCT function is returning the row number of that matching price tier. That row number is used as the index of the item in the specified array within the INDEX function, which if you start at a row other than 1 will be the incorrect index.
0
Martin LissOlder than dirtCommented:
I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.