Combining LOOKUP and OR (in lookup_vector?)

[Richard JIBS]

Hello,

I have a spreadsheet that looks like this. The first number in column B (eg 20) is entered manually, then the numbers below it automatically become -1 the nearest cell above that does not contain "n" once the date in column A is reached. I have this working fine.

COLUMN A	COLUMN B
2016-05-02 [=TODAY()]	SESSION
2016-03-21	20
2016-03-28	19
2016-04-04	18
2016-04-11	n
2016-04-18	17
2016-04-25	x
2016-05-02	16
2016-05-09

<tbody>
</tbody>

In column B I currently have this formula filled down:
=IF(A3<=A$1,LOOKUP(2,1/($B$2:B2<>"n"),$B$2:B2-1),"")

This works for cells containing "n". But I need it to work for "n" OR "x". In the example above I can't get session 16 to appear as it follows an x.

I have tried adding OR in different places but it doesn't seem to be working – I could be doing it wrong though!

Could you help me amend the formula to allow for "n" or "x" please?

Thanks,

Richard

 

[ghrain22]

Try:
=IF(A3<=A$1,LOOKUP(2,1/(or($B$2:B2<>{"n","x"})),$B$2:B2-1),"")

 

[Richard JIBS]

It doesn't seem to work in cell B8, where I want 16 to appear.

If I uses this formula it returns 19, as though it can only look at the first cell in the range (B2) and not the most recent cell not containing n or x (B6).
=IF(A8<=A$1,LOOKUP(2,1/(OR($B$2:B7<>{"n","x"})),$B$2:B7-1),"")

 

[Richard JIBS]

Found a solution that works. It's not specifically for n and x, but any text, so still works for my spreadsheet.

=IF(A3<=A$1,LOOKUP(2,1/(NOT(ISTEXT($B$2:B2))),$B$2:B2-1),"")