Forum Discussion
Provide a Value when number is within a specific range (min-max) in other columns
Your Min and Max values overlap: 45 is both Red and Blue.
Change like this:
Formula in B4:
=XLOOKUP(A4:A9, E4:E8, D4:D8, "", -1)
(Undoubtedly, it is also possible to use a combination of BYROW and LAMBDA 😁)
You are correct that INDEX and MATCH typically look for exact values rather than checking if a number falls within a range. Instead, you can use the LOOKUP or INDEX/MATCH with a condition.
Solution using LOOKUP
If your data is structured like this:
A Value - B Result - D Label - E Min. - F Max.
Use this formula in B2 and drag it down:
=LOOKUP(1, (A2>=E:E)*(A2<=F:F), D:D)
If you prefer INDEX and MATCH, use:
=INDEX(D:D, MATCH(1, (A2>=E:E)*(A2<=F:F), 0))
Enter as an array formula by pressing Ctrl + Shift + Enter (unless using Excel 365 or 2019, where normal Enter works).
My answers are voluntary and without guarantee!
Hope this will help you.
Hey, thanks for the answer, I would like to attach the file to better show what I mean but when I try to do so the website claims the file is not allowed (it's .xlsx), do you happen to know why that is?
That's limitation of this resource. Files attachment is allowed only for user who have some history of activities here, not sure about exact limitations. Alternatively you may publish the file on any other resource ( OneDrive, whatever), make it available for everyone and share the link.
You can upload files to OneDrive or via the web (https://easyupload.io/) and share the link in your post. This might be a temporary solution if you cannot attach files directly.
So I've uploaded the file to the website and sent a reply yesterday, but it was not approved. I understand the restriction for new users, but honestly I'm not sure how I can send the issue to you without this ability. I will try one more time using a screenshot, maybe this can go through.
