Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
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.

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .