I have a range of data max and min from 1000 to 0 corresponding to text identifiers (I, A-F). If I know the price of an item, I want to search for this price in the range and return the corresponding text identifier.
I have uploaded the sheet. I want to search for the item price within the range B:C and return the corresponding grade.
Try this array formula** entered in I4 and copied down:
=INDEX(A$4:A$12,MATCH(1,IF(B$4:B$12>=H4,IF(C$4:C$12<=H4,1)),0))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Tony - Thank you very much for that. That works perfectly. I was struggling with this for a while so your help us much appreciated.
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks