Vlookup/match/rept error

[themightyreds]

Hi all I have the below formula

Worksheet 1
The column A contains the dates from oct to the end of the year, columns B - AP contain trucks and the destinations they went to on each date, Row 96 contains the last destination

Worksheet 2
Contains a table showing the last date and destination for each truck.

To show the last movement date I have used the formula below

=VLOOKUP(INDEX('Entry Sheet'!B$3:B$95,MATCH(REPT("z",90),'Entry Sheet'!B$3:B$95)),'Entry Sheet'!B:AP,37,FALSE)

Problem I am having is it only works half the time. On some it works perfectly on others it returns the date for the first time the truck visited the given destination rather than the last. By that I mean if the truck went to Manchester today it returns the date for the first time the truck went to Manchester. The formula are all constructed the same way so I cannot understand why some work and some fail?

If you have read all the way through this I thank you, If you are able to assist I am very grateful!

 

[Andrew Poulsom]

If what's returned by:

INDEX('Entry Sheet'!B$3:B$95,MATCH(REPT("z",90),'Entry Sheet'!B$3:B$95))

appears more than once in column B your formula will return the first one. For the one on the same row try:

=INDEX('Entry Sheet'!$B$3:$AP$95,MATCH(REPT("z",90),'Entry Sheet'!B$3:B$95),37)

 

[themightyreds]

Sorry for my lack of response Andrew a trip to Beirut interrupted my train of thought.

Your formula

=INDEX('Entry Sheet'!$B$3:$AP$95,MATCH(REPT("z",90),'Entry Sheet'!B$3:B$95),37)

returns the last destination rather than the date (which is in column A)

I enclosed it in a vlookup which seemed to work on unique destinations only?

=VLOOKUP(INDEX('Entry Sheet'!B$3:B$95,MATCH(REPT("z",90),'Entry Sheet'!B$3:B$95)),'Entry Sheet'!D:AP,39,FALSE)

I am using =INDEX(B3:B95,MATCH(REPT("z",90),B3:B95)) to identify the last movement but need a formula to identify the date of the last movement.

 

[Andrew Poulsom]

I don't see that my formula is much different from yours. Both return an entry from column 37 in the range B:AP. Neither formula references column A.

 

[themightyreds]

Sorry being a clot, yes you are right there is an additional line of dates of column 37, I understand now, thanks Andrew you are perfectly right of course.