up vote 1 down vote favorite
2
share [g+] share [fb]

I need to compare two lists of values to determine whether they are the same or not in excel. Each value is in a different horizontal cell as shown in my illustration. How can I build a formula to do this task.

1 2 3 4 5           should say No
1 2 3 5 4

4 5 1 2 3           should say Yes
4 5 1 2 3
link|improve this question
53% accept rate
feedback

1 Answer

up vote 8 down vote accepted

You can use this formula:

=IF(SUMPRODUCT(1*(A1:D1<>A2:D2))=0, "Yes", "No")

SUMPRODUCT is a special function that works on an array basis, it ends up comparing each set of cells to each other, e.g. A1<>A2, B1<>B2, etc.

link|improve this answer
Nice! I've needed to do this many times and have always relied on a ugly set of vlookup and iif formulas. This is much better, thanks @Lance. – StevenV Aug 10 '11 at 17:09
How would you approach this problem if it weren't values but text? – Flethuseo Sep 6 '11 at 23:53
@Flethuseo, it also works for text as written. – Lance Roberts Sep 7 '11 at 1:37
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.