Return value based on last date entered

[gotroots]

Hi,

This is my first post to this forum. I have a question. How do I return a value from another sheet based on two conditions.


Return the most recent Qty (by date & Colour) from sheet2


(sheet1)
Colour Qty
red << formula to return 765
green << formula to return 65
blue << formula to return 855
white << formula to return 86
orange << formula to return 83
black << formula to return 3


(sheet2)
Date QTY Colour
02/12/2018 499 red
02/12/2018 32 green
02/12/2018 567 blue
02/12/2018 234 white
02/12/2018 12 orange
02/12/2018 67 black
25/04/2019 58 red
25/04/2019 778 green
25/04/2019 96 blue
25/04/2019 457 white
25/04/2019 368 orange
25/04/2019 44 black
03/09/2019 765 red
03/09/2019 65 green
03/09/2019 855 blue
03/09/2019 86 white
03/09/2019 83 orange
03/09/2019 3 black



Kind regards

[BadlySpelledBuoy]

Hi and welcome to the forum.

Will your data on sheet two always be in date order like that?

BSB

[AliGW]

Welcome to the forum!

Are you wanting to return the value of the latest date per colour?

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[BadlySpelledBuoy]

If your data will always be in that order (i.e. latest dates at the bottom of the data) then you could use something like this:
=LOOKUP(2,1/(Sheet2!C$2:C$19=A2),Sheet2!B$2:B$19)
See attached.

If the data could be in another order then you'll need something different.

BSB

[gotroots]

Hi and thank you for making me feel welcome,

BadlySpelledBuoy - with some changes your formula has the makings of what I need. I need both ranges to be indefinite but am getting an error with this change =LOOKUP(2,1/(Sheet2!C$2:C=A2),Sheet2!B$2:B)

AliGW - I had tried to include a sample workbook but was prevented from including it with my message as a new member I need to be posting for a few times first before I can do so.

[BadlySpelledBuoy]

Maybe this?
=LOOKUP(2,1/(Sheet2!C:C=A2),Sheet2!B:B)

Or if calculating on entire columns causes you issues you could use dynamic named ranges to only include down to the last populated row...

BSB

[gotroots]

The column on my working workbook are not beside each other colG and colK (both starting on reow5) on sheet2 . In sheet1 starting on row5 using colI for the formula and colO for the colour. I changed the formula form =LOOKUP(2,1..... to =LOOKUP(5,8.....

Am I correct to assume 2 represents the 2nd row and 1 is cola

[BadlySpelledBuoy]

Am I correct to assume 2 represents the 2nd row and 1 is cola

Incorrect. Have a look at the link below which will explain how it works.
https://www.exceltip.com/excel-formu...ing-value.html

You just need to change the column references to suit your data layout. Don't worry about row references.

If you're still stuck then click "Go Advanced" below your reply then scroll down and click on "Manage Attachments" to open the upload window.
The paperclip icon doesn't work for attaching files.

BSB

[gotroots]

Success! your link explaining how it all works was a great help, thanks.

Next time I need help I will attach an excel file as you both have instructed for me to do.

Cheers!

[BadlySpelledBuoy]

Happy to help

BSB

[AliGW]

AliGW - I had tried to include a sample workbook but was prevented from including it with my message as a new member I need to be posting for a few times first before I can do so.

There is no restriction on new members posting attachments - instructions about how to do so we’re given in post #2. New members are not permitted to post links, however.