Vlookup and return latest update?

[Ashe77]

Hi, I wonder if anyone can help me, I'm pretty sure it's actually a simple solution but it's beyone me.

I have a workbook and the first sheet shows a list of retailers and various details whereas the second sheet lists the same retailers and weekly status updates.
I want sheet 1 to be able to look into sheet 2, match the retailer name and return the latest (i guess last filled cell in the relevant column) and return the latest update to sheet 1.

Example attached.

If anyone can help, I will owe you great thanks.


Regards

Paul

[:) Sixthsense :)]

Not sure which data needs to be matched with which column

[Fotis1991]

Hi Paul

Which is the expected result in B3 and why..?

[Ashe77]

Hi FOtis

In this example instance, the result would be the "I don't know" content of B8 on "Update" (of course as long as the correct retailer name is selected in "Main"). Naturally, if then B9 were to be filled, I would want the relevant cell in B on "Main" to then reflect that as it would then be the "latest update" if you will.

Hope I've explained that ok.

Paul

[Ashe77]

Sorry, I've noticed in rushing, the example document was a mess. I've added a bit more in now, might make more sense. So as above, Main tab Column B would look up the retailer on Tab Updates and return the latest text filled update in the related column.

Regards

Paul

[Fotis1991]

Sorry, I've noticed in rushing, the example document was a mess. I've added a bit more in now, might make more sense. So as above, Main tab Column B would look up the retailer on Tab Updates and return the latest text filled update in the related column.

Regards

Paul

Paul. Where are the retailers Names in Update tab?

[Ashe77]

Hi Fotis

On the columns from B2 through U2. These match (in content) the list on Main tab in column A which is in a data validation drop-down. To be honest, there may be some that don't match entirely in terms of the text, that I have just noticed as I have re-cut and pasted this several times to try formulas.

Paul

[Fotis1991]

Paul as you see i do want to help but this makes no sense to me.

So one more try. Pls, put manually the expected results in your sample sheet explaining as much better as you can, WHY, these must be the results.

[Ashe77]

Ho Fotis

Sorry, I am most likely making this more complex or badly explained than I should. Apologies, I was rushing before.

Ok, so the concept is that the first tab (Main) in column A has a drop down list which allows me to select one of the retailers all of whom are stored on the 3rd tab (retailers and epos). The 2nd tab (Updates) would be filled out by a colleague with status updates on these retailers as to current progress with their business.
My desire is that when I populate column A on Main tab with one of the retailers from the list, it automatiocally populates Column B with the most recent update entered on Tab Updates for that retailer.

So for example, on June 21st "Bestway" has a latest update of "I don't know" and Co-Op Anglia has a latest update of "test 4". I want it to be that when on Main tab I choose "Bestway", Column B auto populates "I don't know" as the latest update. Similarly if I chose "Co-Op Chelmsford" it would populate "result" as the latest update (as per the info on the updates tab).

The exercise is purely for automation to avoid mistakes and allow multiple users to use the form easily.

Paul

[Fotis1991]

Now, makes sense.

We Use INDEX& MATCH and also MAX(IF....

[Ashe77]

Fortis

That is amazing, thank you. I could never have worked that one out myself.

One question - on the document I notice that when I copy down the formula below line 6, it doesn't seem to return the result. Am I doing something incorrect there?

Paul

[Fotis1991]

Have you any data below A6?

[Ashe77]

Initially no but I have dragged the cell down to copy the data validation from A2-A6 and done the same for column B but when I then select one of the list in A7, B7 does not populate.

Paul

[Hurricanefly]

You have a date in column C? in the MAIN spreadsheet?

[Hurricanefly]

The formula works fine on the example I downloaded, just add the date in column c on your main spreadsheet.

[Ashe77]

I now see, I now see! I did not notice column C hidden therefore was not copying that down also.

Thank you both for all your help on this, it is hugely appreciated.

[Fotis1991]

......................