Possible VLookup IF Equation

[Michael151]

I have two lists that I'd like to compare, each with 3 columns.

First list:
Title1 Date1 Date1.1
Title2 Date2 Date2.2
Title3 Date3 Date 3.3
Title4 Date4 Date 4.4

Second List:
Title2 Date2 Date2.2
Title4 Date4 Date 4.4

What I'd like to have is a column next to the second list with a "true/false" result that says, if the title in the second list is in the first list and the two dates columns also match, return a "true" result.

I'd use a simple if(and equation, but my problem is that the titles do not match (the second list is shorter than the first list).

Any help would be most appreciated!

 

[FryGirl]

Hi Michael,

My choice would be to concatenate your lists and then you can use

=ISNUMBER(MATCH(K1,$E$1:$E$4,0))

E1:E4 holds the concatenated list from list 1 and K1:K2 holds list 2 concatenated.

 

[Michael151]

Thanks FryGirl!

Sorry but how would I concantenate the lists?

Also would ISNUMBER work for the titles in column 1? These are text, not numbers.

Thanks for your help.

 

[FryGirl]

That's okay they are text, the match function is just retuning the oridinal position number.

In your test data list 2 matches list 1 in position 2 and 4. This isnumber just changes the 2 and 4 to True/False

Try entering the formula and then with and without the isnumber.

To concatenate: E1 = A1&B1&C1 and then K1 = G1&H1&I1

 

[Michael151]

Any way to do this without using the concantenate function?

It's fine if I have three separate true/false columns.

Thanks!

 

[Michael151]

it would be something like:

IF (Title somewhere in list 2) matches (Title in list 1) AND (date1 matches date1) AND (date2 matches date2), THEN True or False

 

[FryGirl]

Not sure what columns you have your data in, but I can't think of any elegant solution without using the helper column.

This will work

=ISNUMBER(MATCH(F1,A1:A4,0)+MATCH(G1,B1:B4,0)+MATCH(H1,C1:C4,0))

This will combine the three columns so you will only have one true/false, but if you don't mind the three columns then just seperate the formulas.