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]

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.

Sorry I should have made it clear that the first task of the formula is to look for the first and second values in the same row in COLUMN 1 and COLUMN 2.
It is then looking for these same values in the same row in COLUMNS 3 and 4.

 

[Tetra201]

Try this:

=AND(SUMPRODUCT(--($A$2:$A$100&$B$2:$B$100=C2&D2)))

 

[harvw]

Try this:

=AND(SUMPRODUCT(--($A$2:$A$100&$B$2:$B$100=C2&D2)))

Thanks. The formula works like a treat!

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?

 

[Tetra201]

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|...

 

[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|...

Thanks again man!

Works brilliantly!

 

[Tetra201]

You are most welcome.

 

[harvw]

You are most welcome.

Sorry.

Have another question around this.

Let's say COLUMN 1 and COLUMN 2 are in one sheet and COLUMN 3 and COLUMN 4 are in another sheet. How would this formula work then?

 

[Tetra201]

... Let's say COLUMN 1 and COLUMN 2 are in one sheet and COLUMN 3 and COLUMN 4 are in another sheet. How would this formula work then?

Like this:

=AND(SUMPRODUCT(--(Sheet1!$A$2:$A$100&"-"&Sheet1!$B$2:$B$100=Sheet2!C2&"-"&Sheet2!D2)))

 

[harvw]

Like this:

=AND(SUMPRODUCT(--(Sheet1!$A$2:$A$100&"-"&Sheet1!$B$2:$B$100=Sheet2!C2&"-"&Sheet2!D2)))

Thanks again.