Excel formula to return the latest data in a row range that is not equal to a specific word

[p3gladiator]

Hello all,

I need some help, please.

I have a spreadsheet that has a list of several hundred employees in one column and columns for each payroll week with an "A", for active "T" for Termed and "NOR" for Not on report.
I need an excel formula that will return the last data entered that is not equal to "NOR".

Example:

Name	08/05/2021	08/12/2021	08/19/2021	08/26/2021	09/02/2021	09/02/2021	09/09/2021	09/16/2021	09/23/2021	09/30/2021	Need formula that will return last A or T
Mickey mouse	A	A	A	A	T	NOR	NOR	NOR	NOR	NOR	T
Donald Duck	A	A	A	A	A	A	A	A	A	NOR	A

Thanks for your assistance

 

[JEC]

When your data starts in A1:

=LOOKUP(2,1/(B2:K2<>"NOR"),$B$1:$K$1)

 

[p3gladiator]

Thank you so much but, can you please tell me what the 2,1 is referring to? I understand the rest of it.
my range for the first person is F3:AH3.

Thanks again

 

[JordsdoExcel]

Hello friend

See if is this that are you looking for:

Mickey Mouse - =INDEX($B$1:$K$1,0,LARGE(IF(B2:K2="A",COLUMN($A$4:$J$4),IF(B2:K2="T",COLUMN(B2:K2),FALSE)),1))
Donald Duck - ==INDEX($B$1:$K$1,0,LARGE(IF(B3:K3="A",COLUMN($A$4:$J$4),IF(B3:K3="T",COLUMN(B3:K3),FALSE)),1))

 

[p3gladiator]

Hello friend

See if is this that are you looking for:

Mickey Mouse - =INDEX($B$1:$K$1,0,LARGE(IF(B2:K2="A",COLUMN($A$4:$J$4),IF(B2:K2="T",COLUMN(B2:K2),FALSE)),1))
Donald Duck - ==INDEX($B$1:$K$1,0,LARGE(IF(B3:K3="A",COLUMN($A$4:$J$4),IF(B3:K3="T",COLUMN(B3:K3),FALSE)),1))

thank you, this is awesome but, this returns the date and I need it to return the A or T.

 

[JEC]

• If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.

So the formula is searching for the first 1 in the searcharray. From right to left.

=LOOKUP(2,1/((F3:AH3<>"NOR")*(F3:AH3<>"")),F3:AH3)

 

[p3gladiator]

• If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.

So the formula is searching for the first 1 in the searcharray. From right to left.

=LOOKUP(2,1/((F3:AH3<>"NOR")*(F3:AH3<>"")),F3:AH3)

Thank you very much and for the explaination! Your Formula worked perfectly!

 

[JEC]

You're welcome!