need a formula

Copper Contributor

EXCEL.PNG

 

8 Replies

@suyankjain =XLOOKUP("TINA",B:B,A:A,0,0,-1)

 

IS THIS FORMULA IS ONLY COMPATILBLE FOR EXCEL 365 USERS?

BECUASE ITS NOT WORKING

@wsantos 

@suyankjain Not sure if you need O365, or just a new version of excel. I don't know of another way of doing this, sorry.

@suyankjain This formula will find the value in A for the last entry "TINA" in B.

=INDEX(A:A,MAX((B:B="TINA")*ROW(B:B)),1)

If you have an older version of Excel you may have to confirm entry with Ctrl-Shift-Enter. That will put "curly brackets" around it. Not sure though.

@suyankjain 

As variant, the regular formula is

=LOOKUP(2,1/(B:B="TINA"),A:A)

 

@Sergei Baklan what kind of sourcery is this? I tried to research how it works but can't even figure out how to explain what i'm seeing... 2nd argument would result either in 1 or div/0.

@wsantos 

Second parameters always returns error or 1. LOOKUP ignores all errors and works from left to the right, thus since we lookup value 2 never will be found, LOOKUP returns the last appeared. Since we have third parameter, it returns the value form it taken for the position of last appeared in second parameter.

@Sergei Baklan thanks for the help