Searching for multiple values in a "LOOKUP" function formula

Hi,

I've developed a formula which looks for the LAST/FINAL value in an excel range that is equal to 1 and it works perfectly! I've used LOOKUP instead of MATCH as I am trying to find the final value, not the first. Here is my current working formula:

=LOOKUP(2,1/([VALUE RANGE I AM SEARCHING IN]=1),[FINAL VALUE DISPLAYED IN CELL])

Basically, if row B contains 20 numbers (and includes seven "1" values), it will search for the final value that displayed the number "1". The cell would then display the date above in row A from the last number "1" found.

However I want to adjust this formula so I can find the final value if it is equal to 1, 2 or 3. I've tried the following but obviously it doesn't work:

=LOOKUP(2,1/([VALUE RANGE I AM SEARCHING IN]=1,2,3),[FINAL VALUE DISPLAYED IN CELL])

Thanks in advance for your help, it's a pretty complicated formula but works for exactly what I need

Hi,

Two options:

=LOOKUP(2,1/ISNUMBER(MATCH(A1:A20,{1,2,3},0)),B1:B20)

=LOOKUP(2,1/MMULT(N(A1:A20={1,2,3}),{1;1;1}),B1:B20)

Regards

Ah, yes - I got the ranges the wrong way round.

Anyway, you're welcome!

Cheers