CODEWITHSUNDEEP
excelexcel-formularange
Owlythesova
Owlythesova

Reputation: 75

IF function with two cell ranges

I have two sheets with the same line of cells, for example, A1:A5. I need to check if the value of every cell in Sheet1!A1:A5 is equal to Sheet2!A1:A5 but the hitch is the values will be letters, and all values are different. Simply typing the formula got me a #VALUE! error.

I know I can just write the formula: 

=IF(Sheet1!A1=Sheet2!A1;1;0)

and then simply retype it in a number of cells with different values, but I'm looking for a way to shorten the formula.

Any suggestions?

Upvotes: 1

Views: 142

Answers (4)

PankajR
PankajR

Reputation: 407

To shorten the formula use array function. With that you will be able to check the whole range at once.

=IF(AND(Sheet1!A1:A5=Sheet2!A1:A5);1;0)

After typing the formula press Ctrl+Shift+Enter instead of just Enter key to confirm array formula.

Upvotes: 2

Rosetta
Rosetta

Reputation: 2725

Use the AND() function:

IF(AND(Sheet1!A1=Sheet2!A1,Sheet1!A2=Sheet2!A2,Sheet1!A3=Sheet2!A3,Sheet1!A4=Sheet2!A4,Sheet1!A5=Sheet2!A5),1,0).

EDIT

Not realy sure about your aim,

If you want it short because it is too difficult to write the above function, then try the method below:

=IF(CONCATENATE(Sheet2!A1,Sheet2!B1,Sheet2!C1,Sheet2!D1,Sheet2!E1)=CONCATENATE(Sheet1!A1,Sheet1!B1,Sheet1!C1,Sheet1!D1,Sheet1!E1),1,0)

But this is not without catch, it could return false positive. So use it with care. To overcome the false positive, I could only make the formula longer (but still relatively easy to write out).

=IF(CONCATENATE(Sheet2!A1,"|",Sheet2!B1,"|",Sheet2!C1,"|",Sheet2!D1,"|",Sheet2!E1)=CONCATENATE(Sheet1!A1,"|",Sheet1!B1,"|",Sheet1!C1,"|",Sheet1!D1,"|",Sheet1!E1),1,0)

Upvotes: 0

jimkokko5
jimkokko5

Reputation: 88

You could use 

AND(EXACT(Sheet1!A1, Sheet2!A1), EXACT(Sheet1!A2, Sheet2!A2), EXACT(Sheet1!A3, Sheet2!A3), EXACT(Sheet1!A4, Sheet2!A4), EXACT(Sheet1!A5, Sheet2!A5))

But in the following way:

  1. Have a separate column with the code (let's say, column G)

    EXACT(Sheet1!$A1, Sheet2!$A2)

  2. To the column next to that, have a single cell with the code

    AND(G1:G5)

Upvotes: 0

pcofre
pcofre

Reputation: 4066

This one is a little shorter

=(Sheet1!$A1=Sheet2!$A1)

Upvotes: 0

Related Questions