Index match, xlookup or both?

[levy77]

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.

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.

 

[jasonb75]

I think this should do it (not tested)

=LOOKUP(1e+100,INDEX(B5:G5,1,MATCH(B1,B4:G4,0)):INDEX(B5:G8,MATCH(B2,A5:A8,0),MATCH(B1,B4:G4,0)))

It looks like your numbers are formatted as text in the example provided, if that is the case then this formula will not work.