A Jedi Mind Trick For Excel. VLookup The Last Occurrence

[Eddiegnz1]

VLOOKUP(A1,[InADifferentWorkbook.xlsx]Sheet2'!A1:E2000,5,FALSE)

The above VLOOKUP formula is in the main workbook and the data set is In "A Different Workbook". The value that is being looked up is a loan number in column 'A' (7000999999). The value that I want to return is the date in column 'E'. The loan numbers can occur more than once in column 'A' but I only need the last occurrence because the dates are sorted in column E from oldest down to most recent.

To me, it seems like this would require Jedi Mind Tricks !

Kindest thanks,
Eddie

[daffodil11]

VLOOKUP returns the first match, where LOOKUP returns the last.

Use LOOKUP instead.

=LOOKUP(lookup value, lookup vector, return vector)

=VLOOKUP(A1,[InADifferentWorkbook.xlsx]Sheet2'!A1:A2000,[InADifferentWorkbook.xlsx]Sheet2'!E1:E2000)

[boopathiraja]

Try the below array formula, (to be entered with ctrl+shift+enter not just enter)

Please Login or Register  to view this content.

[boopathiraja]

May be this for your ranges

Please Login or Register  to view this content.

[Eddiegnz1]

Ah, now i feel like "Salacious Crumb" (Jabba The Hut's pet)

thanks so much y'all. worked great