CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
cyborg
cyborg

Reputation: 491

How to do VLOOKUP based on multiple criteria and return the latest rows result?

Currently when I use Vlookup, it provides for first matching occurrence of data.

I receive data into a spreadsheet by date, i.e., a record existing for every project with values for status field. I build a consolidated report in a new tab at project level, I need to populate the 'STATUS' column for each project.

The status field value could remain same for few days but would change on a certain date. If I use VLOOKP(Project#, SampleData! Project#:Status, 7 , 0) I would get for first match i.e., most likely will get always 'In-Progress' as result

How can I add another criteria in VLOOKUP to search for max of dates upon the matching project# & return the result?

Google Sheet Link

Sample Raw Data

Consolidated View

Upvotes: 0

Views: 2087

Answers (2)

basic
basic

Reputation: 11968

If projects are sorted by date in ascending order, you can use LOOKUP function to get last status:

=ArrayFormula(LOOKUP(2,1/(A11=$B$2:$B$7),$H$2:$H$7))

if not sorted, then try following:

=ArrayFormula(LOOKUP(2,1/((A11=$B$2:$B$7)*(MAX($A$2:$A$7*(A11=$B$2:$B$7))=$A$2:$A$7)),$H$2:$H$7))

enter image description here

Explanation:

Here is used LOOKUP feature if search key is not found, the lookup will return the last closest matching key in the range with a lower value. In first formula LOOKUP search for value 2 while expression 1/(A11=$B$2:$B$7) returns array with errors and ones {1/TRUE,1/TRUE,1/TRUE,1/FALSE,1/FALSE,1/FALSE} => {1,1,1,DIV/0,DIV/0,DIV/0} so LOOKUP returns value from third row in range $H$2:$H$7.

In the second formula, by analogy.

Upvotes: 1

Alessandro
Alessandro

Reputation: 2998

Using VLOOKUP

Leveraging the ARRAYFORMULA function you can build a search_key for your range:

This will basically multiply the Boolean result of the EQ function (=) for all the possible dates and then take the maximum.

Note: Boolean values are also represented as 1 for TRUE and 0 for FALSE, so every date that is not for the corresponding Project code will result in a very old timestamp.

I will then use this as a search_key to get the status (col 8) for the Project code subset range (built with the IF function):

=ARRAYFORMULA(
     VLOOKUP(
         MAX($A$2:$A$7*(A11=$B$2:$B$7)), 
         IF(A11=$B$2:$B$7, $A$2:$H$7),
         8,
         FALSE
     )
)

Dragging down this formula for each project code will return its last status.

Using QUERY

If you prefer to use the QUERY formula instead of the VLOOKUP here is an example:

The query is based on a sub-query that basically leverages the max() aggregate function to find the max date for a specific Project code.

Then it uses this maximum date and the Project code as a key to be sure to exclude date duplicates that belong to other Project codes.

=QUERY(
    $A$1:$H$7, 
    "select H where A = date '"& TEXT(
        Query(
             $A$2:$H$7,
             "select max(A) where B = "&A11&" label max(A) ''",
             1
        ),
    "yyyy-MM-dd")&"'",
     0
)

Here as well dragging down the formula will return the Project last status.

References

VLOOKUP

QUERY

IF

Upvotes: 1

Related Questions