Vlookup with extra criteria

daftwood

Board Regular
Joined
Feb 4, 2004
Messages
67
Hi, I've looked around but can't find anything that helps me solve my problem.

Source columns:

LookupText | SourceDate

Lookup columns:

LookupText | LookupDate | Value

I want to return Value where LookupText is the same and SourceDate is <= LookupDate

Any help would be very gratefully received!

Thanks

Martyn
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
There may be better ways of doing this, but this seems to work.

Assumptions:
Lookup data is in the range A1:C10, text in A, dates in B, values in C
Source data is in the range E1:F1, text in E, date in F
There is only ever one single value that matches the criteria of matching text, and date <= target.
IF more than one value matches the criteria, the values will be added together.

=SUMPRODUCT(--(A1:A10=E1),--(B1:B10<=F1),C1:C10)
 

daftwood

Board Regular
Joined
Feb 4, 2004
Messages
67
Thanks Gerald. Unfortunately I fall over at the assumption there's only ever one single value that matches!

However I think I might be able to get this to work for me by creating a sum of values based on LookupDate and LookupText in my Lookup sheet.
 

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
So if there's more than one single value that matches the criteria, what do you want to do ?
 

daftwood

Board Regular
Joined
Feb 4, 2004
Messages
67
I'd like the value that corresponds to the highest LookUp date so was going along the lines of sorting by LookupText then LookupDate descending.
 

daftwood

Board Regular
Joined
Feb 4, 2004
Messages
67
I'd like the value that corresponds to the highest LookUp date so was going along the lines of sorting by LookupText then LookupDate descending.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,971
Messages
5,411,581
Members
403,380
Latest member
ifog671

This Week's Hot Topics

Top