Urgent Excel Formula Help Needed!

[harvw]

Hi Guys,


I am trying to develop a formula which can help me do the following:


1. Looks up for two specific values in two separate columns
2. Looks for the first value in a third column and also looks for the second value in a fourth value
3. If both the first value and second value are found in the same row in the third and fourth columns, then the formula should return TRUE, if not, then it should return FALSE


I've tried to illustrate the example below.



COLUMN 1 COLUMN 2 COLUMN 3 COLUMN 4


10002334 50 10002334 55
10003445 60 10002334 75
10002456 70 10002566 40
10004567 55 10002344 50 (SHOULD MATCH)
10004513 66 10004513 78
10008907 41 10004513 66 (SHOULD MATCH)


Thanks for your help in advance.


I look forward to all of your responses.

 

[harvw]

Out of interest, if I wanted to do this for multiple columns, i.e. look for eight different values in the same row (C1, C2, C3, C4, C5, C6, C7 C8) and then find exactly the same values in eight different columns (C9, C10, C11, C12, C13, C14, C15, C16) what would the structure of this formula look like?

I want to create a separate column which returns either TRUE if all of the values in one row within sheet 2 (C1, C2, C3, C4, C5, C6, C7 C8) equal ALL of the values in any row in sheet 1 or else FALSE. Please note however, that the number of rows in sheet 2 is 200 and the number of rows in sheet 1 is 400. Therefore, the formula will need to search all 400 rows in sheet 1 to check if any of the rows (and all values contained within) in sheet 2 can be found in the 400 rows before determining whether to return TRUE or FALSE.

 

[harvw]

Maybe this:

=AND(SUMPRODUCT(--($A$2:$A$100&"-"&$B$2:$B$100&"-"&$C$2:$C$100&"-"&$D$2:$D$100&"-"&$E$2:$E$100&"-"&$F$2:$F$100&"-"&$G$2:$G$100&"-"&$H$2:$H$100=I2&"-"&J2&"-"&K2&"-"&L2&"-"&M2&"-"&N2&"-"&O2&"-"&P2)))

I have added dashes to handle situations like |123|456|... vs |1234|56|...

What if I was comparing the values in two different sheets?