I have a table that looks something like this
| ID | Values 1 | Values 2 | Values 3 |
|---|---|---|---|
| 1 | 123 | 456 | 789 |
| 2 | 234 | 567 | 890 |
| 3 | 345 | 678 | 901 |
| 4 | 678 | 901 | 234 |
I'm using ID value to locate the row of interest (e.g. row ID=3) via either:
=CHOOSEROWS($A$1:$D$5,3)
or
=FILTER($A$1:$D$5,$A$1:$A$5=3)
I get a spill, say in row 6, that looks like this:
3 345 678 901
I'm trying to select a value from this row that is either matching or is next larger compared to a random number. Say, for a random number of 500 I would like to select the value 678. I'm trying:
=XLOOKUP(500,$A$6:$D$6,$A$6:$D$6,,1,)
but I'm always getting a result that is several columns off from what I'm trying to obtain (my actual table is quite bigger than this example). In this example I would get a number for Value 5, Value 6 and so on. What's even worse, this offset is always different based on which row I originally selected. Anyone has an idea how to make this work?