Forum Discussion
Solved
Provide a Value when number is within a specific range (min-max) in other columns
Hi there, I am trying to get a result in column B taken from values set in column D, based on number in column A. In column A there are numbers within ranges set in min-max columns E and F. I am attaching an example to better showcase what I am trying to do. I have tried to do this with INDEX and MATCH formulas, but I assume the issue is that it doesn't look for a number within a range of numbers but an exact numbers, so I am missing something.
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 😁)
I don't see an example...
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.
