Lookup value based on two column criteria

[Dcap]

i have been trying to solve the following issue:

i have a very long column of values (in this case there are only two values = FY23, FY24, with FY23 always coming before FY24) and another column values (all zeroes except one value >0 in an FY23 row and one value >0 in an FY24 row)



i want to create a lookup formula that searches the two columns to return the actual >0 value for both the FY23 and the FY24 rows



small example

ColA ColB

FY23 0

FY23 0

FY23 5

FY24 0

FY24 0

FY24 0

FY24 9



i have used IF/INDEX/MATCH with an else - and it returns the first value for FY23 (5), but for the second value it returns false

=IF(ColA="FY23",INDEX(ColB,MATCH(TRUE,ColB>0,0)),"Pending") will return 5

=IF(ColA="FY24",INDEX(ColB,MATCH(TRUE,ColB>0,0)),"Pending") will return Pending instead of 9

Note: both use Cntl-Shift-Enter



i see what it is doing - it is counting the match down to the 5 (giving it row 3) and then it sees FY23 in ColA - hence the return of Pending
i cant figure out how to get it to go to FY24

i have tried using VLOOKUP but have gotten nowhere

 

[Cubist]

Try:

Book1
	A	B	C	D	E
1	FY23	0		5	9
2	FY23	0
3	FY23	5
4	FY24	0
5	FY24	0
6	FY24	0
7	FY24	9
Sheet6

Cell Formulas
Range	Formula
D1:E1	D1	=XLOOKUP(TRUE,B1:B7>0,B1:B7,,,{1,-1})
Dynamic array formulas.

 

[Dcap]

Try:

Book1
	A	B	C	D	E
1	FY23	0		5	9
2	FY23	0
3	FY23	5
4	FY24	0
5	FY24	0
6	FY24	0
7	FY24	9
Sheet6

Cell Formulas
Range	Formula
D1:E1	D1	=XLOOKUP(TRUE,B1:B7>0,B1:B7,,,{1,-1})
Dynamic array formulas.

that worked - thanks so much
i dont quite understand what the formula is doing but it worked -
much appreciated

 

[Cubist]

You're welcome. The formula checks each cell is >0, then looks for the first and last TRUE. Return the values.