Thread: vlookup in descending order

Hi guys. Fancy something to get your teeth into?

Im running a workbook that has a list of all the runs for a day, and several daily driver sheets that automatically copy and order the drops using vlookup.

If you look at run 1 on the run listing you will see that holmfirth is the first drop and sandbach is the last drop.
This is also the case on page 1, as it should be.

However, on run 7 you will see that the first drop is Edinburgh and the last drop is Biggar.
The problem is that on the run listing form, we sort the run backwards so that it is loaded onto the trunk wagon backwards, then the load is tran-shipped (swapped) to the delivery wagon (dont ask) and the last drop on the trunk wagon (Biggar on the front end) becomes the first drop(on the back end) on the delivery wagon now in the correct delivery order. I need the daily sheet (page 7) to reflect the fact that Biggar is the first drop and Edinburgh is the last drop.

Hope this helps.

Colin

I am not 100% clear on the dilemma.. what should we see, where and why?

Sorry for not being clear, I have re typed first message

In sheet 7, A14 enter:

=IF(ISNUMBER(MATCH(A13+1,'Run Listing'!$A$63:$A$80,0)),A13+1,"")

copied down... this avoids the errors at the bottom so you don't need to conditionally format, which takes up unnecessary resources.

Then in B14, try:

=IF($A14="","",VLOOKUP(LARGE($A$14:$A$31,ROWS($A$14:$A14)),Run7,2,0))

copied down

in C14:

=IF($A14="","",VLOOKUP(LARGE($A$14:$A$31,ROWS($A$14:$A14)),Run7,3,0))

copied down.

You can adapt to other sheets and replace other Vlookups in the same sheet to include the =IF($A14="","".... part so you can remove errors and conditional formatting...

Oh!!! just had another thought.
If i had a collection in the middle of the run and wanted to highlight it, lets say by putting in "COL" on the listing page and highlighting the copied cell in bold red on page 7

Ive cheated on the sheet attached, but can I go into format - conditional format in the normal manner or is it a little more involved?

Kind Regards

Colin

After selecting the range, go to conditional formatting and select Cell Value is >> Equal to >> and enter ="COL"

Then format as Red font.