View Full Version : Yet another XL formula rqd.

28-02-2005, 08:41 AM
I give up, I think senility has well and truly set in!

All I want to do is return a 1 if three numbers in column A match three numbers in column B, regardless of order, and 0 if they donít.



1 3
2 2
3 1

This I want to produce a 1

1 3
2 15
3 1

This I want to produce a 0

Now, quite why I canít get this to work eludes me, so rather than tear out what little remains of my hair Iíll seek advice. :)



28-02-2005, 08:48 AM

is I think what you need first result after the condition a1=b1 is if its true 1 and if false 0 secound result

28-02-2005, 09:15 AM
Not quite Beama.

A1,and A2, and A3, must match B1, and B2, and B3, but in no particular order.

28-02-2005, 09:18 AM
Hi Bob

You can do this with a slight variation on the formula from your previous question. But this time there are 6 conditions to test for, rather than just the 2 conditions in your previous question.

Using the previous formula as the basis, you could use something like this :

=IF( OR( AND( A1=B1,A2=B2,A3=B3), AND(A1=B1,A2=B3,A3=B2), AND(A1=B2,A2=B3,A3=B1), AND(A1=B2,A2=B1,A3=B3), AND(A1=B3,A2=B1,A3=B2), AND(A1=B3,A2=B2,A3=B1 )), 1,0)

but that sort of formula will get pretty long-winded if you want to extend your list of numbers much further. If you end up trying to compare great long lists of numbers, then you could try something fancy like this instead :

=IF( AND( SUM(A1:A3) = SUM(B1:B3), MIN(A1:A3) = MIN(B1:B3), MAX(A1:A3) = MAX(B1:B3), AVERAGE(A1:A3) = AVERAGE(B1:B3)), 1,0)

but there is no guarantee it will work 100% of the time for longer lists of numbers.

I'm sure Parry will know a smart function or two to work this out for any length of numbers (no pressure Parry! :D )

HTH, Andrew :)

28-02-2005, 11:46 AM
vlookup maybe ill have a play later sorry busy at the moment

28-02-2005, 01:12 PM
Well done again Andrew, that worked a treat.

I was working along the same lines but including (ďIFíS) which was working fine until I got to the eighth ďIFĒ where I got a big ďbutĒ and no ďmaybeĒ. :)

Beama, I considered trying lookup myself but I think the lookup list has to be in order and in this case that was not guaranteed. Thanks anyway.


28-02-2005, 01:32 PM
Hi, those are some very clever formulas Andrew. :D

I dont think this is better but just a different approach: -

=AND(SUM(A1:A3)=SUM(B1:B3),(MATCH(B1,$A$1:$A$3,0)+ MATCH(B2,$A$1:$A$3,0)+MATCH(B3,$A$1:$A$3,0))=6)

The logic is...

Match will return the position within the range, so if all the positions added together equal 6 (1+2+3) then they must be the same. The sum is required because match will return a hit if say the range b1:b3 was 2,2,2 but if they add up to the same amount then they must be the same numbers (if my arithmatic is correct).


EDIT: btw, this returns True if they are matched or #NA if they are not. This could easily be turned into 1's and 0's if necessary.

28-02-2005, 02:00 PM
Heres with 1's and 0's...

=IF(AND(SUM(A1:A3)=SUM(B1:B3),(MATCH(B1,$A$1:$A$3, 0)+ MATCH(B2,$A$1:$A$3,0)+MATCH(B3,$A$1:$A$3,0))=6),1, 0)