'Formula Help to Find if Number between a Range in 2 Columns and then Offset 1 column
I have 2 groups of columns that are. The first group is a set of 2 columns: 1st column and 2nd columns are both numeric and have a set of search values. The 3rd and 4th columns represent ranges, with the 3rd representing the low/floor value of range, and the 4th column representing the high/ceiling value of range. The 5th column is a unique number attached to that range/row.
The Excel formula checks to see if the 1st column has a value, if not, then the 2nd column, and then searches for that number in the range and returns the unique value from the 5th column.
Solution 1:[1]
You can use XLOOKUP for this.
Formula in G2:
=XLOOKUP(F2,$B$2:$B$5,$A$2:$A$5,"",-1)
The first parameter is the lookup_value, which is just the "Search value" in column F.
The second parameter is the lookup_array, which are the values in column B, the second column of the lookup table.
The third parameter is the return_array, which are the values from the first column of the lookup table.
The fourth parameter is what to return if there is no match.
The fifth pararmeter is important and says to find an exact match or the next smaller item.
You can read more on the documentation page here.
Specifically:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | FlexYourData |