Vlookup with extra criteria

[daftwood]

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

 

[Gerald Higgins]

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]

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]

So if there's more than one single value that matches the criteria, what do you want to do ?

 

[daftwood]

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]

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.