VLOOKUP To Find Last Match

[TFCJamieFay]

Hi All,

I'm looking for a way of doing a VLOOKUP that returns the last match not the first. Is this possible or am I a raving loon for even sugesting it?!

Many thanks,

Jay

 

[Aladin Akyurek]

Hi All,

I'm looking for a way of doing a VLOOKUP that returns the last match not the first. Is this possible or am I a raving loon for even sugesting it?!

Many thanks,

Jay

What is your current VLOOKUP formula?

 

[TFCJamieFay]

I'm just using this...

=IF(ISNA(VLOOKUP($B3,LogInOut!$A$4:$F$1000,6,FALSE)),"-",VLOOKUP($B3,LogInOut!$A$4:$F$1000,6,FALSE))

...and then I manually change any that are wrong (What a nightmare)!!

Basically what the report is, is a login in/out report for a call centre and I need to find the last log out time to calulate the time worked.

Many thanks,

Jay

 

[barry houdini]

So the last time is the latest time? You could use this "array formula"

=IF(ISNA(MATCH($B3,LogInOut!$A$4:$A$1000,0)),"-",MAX(IF(LogInOut!$A$4:$A$1000=$B3,LogInOut!$F$4:$F$1000)))

confirmed with CTRL+SHIFT+ENTER

or to find the last time positionally

=IF(ISNA(MATCH($B3,LogInOut!$A$4:$A$1000,0)),"-",LOOKUP(2,1/(LogInOut!$A$4:$A$1000=$B3),LogInOut!$F$4:$F$1000))

 

[TFCJamieFay]

Many thanks Barry, that worked like a dream!

Jay