I need help for vlookup

[ThrottleWorks]

Sir,


I need help for vlookup, I have a table, column A has names & column B has locations.

The problem is I want to populate latest location for the name, say for example,


Sachin is populated three times in the column A & location is Mumbai Pune Delhi.

I want a formula which will give me the latest location for the Sachin which is Delhi.

I cannot sort the formula & cannot use macro in the file.


Can anyone please help me in this.

 

[Luke M]

Array formula (Ctrl+Shift+Enter):

=INDEX(B:B,MAX(IF(A1:A100="Sachin",ROW(A1:A100))))

 

[Faseeh]

Hi sachinbizboy,


Besides Luke's formula this will also work, assuming your data in column A & B:

=OFFSET(INDIRECT("A"&MATCH("Sachin",A:A,0)),COUNTIF(A:A,"Sachin")-1,1)

Edit: Sorry Luke & sachinbizboy, it will NOT work for non-contiguous ranges of "Sachin", For Contiguous it will work. sorry for interruption!


Regards!

Faseeh

 

[ThrottleWorks]

Hi Extreamally sorry for late reply, this has worked, thank a lot for the support. Have a nice day ahead.

 

[Luke M]

Glad we could help, and thanks for the feedback. =)