Hi, I'm trying to find a way to return a value based on an index match formula, if the returned value is 0 then move up until there is a number.
In the example above I want to look up the value corresponding to column A and row 4 the formula would look something like =INDEX(B5:G8,MATCH(B2,A5:A8,0),match(B1,B5:B8,0))
In this case the returned cell would be B8 which has no value. Because B8="" I want the formula to move up until it finds a number then return that number. In this case, it would try return cell B8 but because that is equal to nothing it will return the cell B7=0.98. Does anyone have any idea of how to go about this? I have played around with ISNUMBER and the reverse search of XLOOKUP but keep getting an =N/A or Value error. Does anyone have any ideas?
Cheers.
| Column look up: | A | |||||
| Row look up: | 4 | |||||
| A | B | C | D | E | F | |
| 1 | 0.34 | 0.36 | 0.24 | 0.75 | 0.37 | 0.12 |
| 2 | 0.15 | 0.74 | 0.35 | 0.01 | 0.02 | |
| 3 | 0.98 | 0.05 | 0.87 | 0.36 | 0.70 | |
| 4 | 0.31 | 0.53 | 0.49 | 0.44 |
In the example above I want to look up the value corresponding to column A and row 4 the formula would look something like =INDEX(B5:G8,MATCH(B2,A5:A8,0),match(B1,B5:B8,0))
In this case the returned cell would be B8 which has no value. Because B8="" I want the formula to move up until it finds a number then return that number. In this case, it would try return cell B8 but because that is equal to nothing it will return the cell B7=0.98. Does anyone have any idea of how to go about this? I have played around with ISNUMBER and the reverse search of XLOOKUP but keep getting an =N/A or Value error. Does anyone have any ideas?
Cheers.
