I have a range with a list of values (depths). I'd like to be able to select the row where the depth matches a value in a cell I provide. If no value matches, the greater value should be selected.
Here's the range.
The solution I found, is to have a temporary area where I revert the order of the range, then I use MATCH
to take advantage of the -1
sort parameter.
Last but not least, I use INDIRECT
to extract the value of a cell in the same row, but a different column.
=IF(ISNUMBER(A2),INDIRECT("SAC!F"&(MATCH(A2, SAC!$E$1:$E$23, -1))),)
Is there a better way to achieve this result?
Examples:
- If the cell has value 6 (exact match), I'd like to get 1.6 (same row)
- If the cell has value 10 (no match), I'd like to get 2.2 (round to 12, then get the factor)