CODEWITHSUNDEEP
excelmatchvlookup
Flavian Hautbois
Flavian Hautbois

Reputation: 3060

Conditional VLOOKUP

I have two excel spreadsheets:

One has an id and a date The other one has an id, two dates and a value

I am supposed to match the ids and return the value whenever the date in spreadsheet 1 is between the two dates in spreadsheet 2.

There is a one-to-one correspondance between those spreadsheets, and a default value of 0 if no match is found

The first spreasheet has a lot of lines. Would there be some sort of VLOOKUP function that I can use to get the right data?

Upvotes: 1

Views: 1323

Answers (2)

Flavian Hautbois
Flavian Hautbois

Reputation: 3060

There was a very simple way to achieve that: concatenate the multiple fields you want to search, and you are done, provided they are unique. Otherwise you should look into array cells.

Upvotes: 2

SeanC
SeanC

Reputation: 15923

SUMPRODUCT and array formula's are what you need.

=SUMPRODUCT(--(Sheet2!$A$2:$A$2000=A2),--(Sheet2!$B$2:$B$2000<B2),--(Sheet2!$C$2:$C$2000>B2),--(Sheet2!$D$2:$D$2000))

this will have to be entered as an array formula with CTRL+SHIFT+Enter. you will see excel surrond the formula with {} if you have entered it correctly.

Assumptions: ID to be checked in A2, Date to be checked in B2
Data on sheet 2 in the order A ID, B start date, C end date, and D value (numeric)

the array formula returns a list of true/false, which is then multiplied together. only if all criteria are True is a value returned (as any False answers equate to zero, and zero * another number is zero).

This will give incorrect answers if 2 ranges overlap

Upvotes: 2

Related Questions