I need help for VLOOKUP follow up question.

[realtop17]

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.

[pre]

column a     column b
sachin       mumbai
sachin       pune
sachin       delhi (is the lateast location)
my follow up qeustion what if i only need is the latest location and no need to post or to display the old location like example below

column a     column b
sachin       n/a
sachin       n/a
sachin       delhi  (is the lateast location)

[/pre]
Regards,

realtop17

 

[realtop17]

[pre]

column a column b
sachin mumbai (old location)
sachin pune   (old location)
sachin delhi (latest location)

my follow up qeustion what if i only need is the latest location and no need to post or to display the old location like example below

this is the out put i want see below
column a column b
sachin n/a  (old location)
sachin n/a   (old location)
sachin delhi (latest location)

[/pre]
Regards,

realtop17

 

[Luke M]

Realtop17,


Taking the formula from previous thread:

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


We can put that inside an IF statement to check if we're at the last record:

=IF(COUNTIF($A$1:A1,A1)<COUNTIF(Old_Data!A:A,A1),"N/A",INDEX(OldData!B:B,MAX(IF(OldData!A$1:A$100=A1,ROW(Old_Data!A$1:A$100))))

 

[realtop17]

WOW!!!!!!AMAZING......... thank you verry much for helping me.... its a big big help

 

[Luke M]

No problem realtop, glad I could help.

Thanks for the feedback!