# Baffled comparing two arrays with IF/AND

#### ejpuck

##### New Member
Hello all,
I am brand new to this board, and my skills are limited. I am having trouble with an IF/And that compares 2 different arrays. The end goal is to determine if there is a spouse, and if that spouse is over 65. If true, I need "1,000" if not, "0".

Array number one is C35:C45 and contains either spouse, child, or nothing.

Array number 2 is I35:I45 and has the age of the corresponding individual from array one.

=IF(C35:C45="spouse","1,000",0) returns true, as does =IF(I35:45>64,"1000",0). But when I entered the same statements as an AND... =AND(C35:45="spouse","1,000",0) even though one of the cells was true, the formula returned false.

So of course when I tried to nest the if/and =IF(AND(C35:C45="spouse",I35:45>64),"1,000",0 it comes back false. Right now c35 says spouse, and I35 says 80.

What am I doing wrong, and how should I write this so it works.

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Maybe

=1000 * sumproduct((C35:C45="spouse")*(I35:I45>=65))

oops, OK, I forgot a very important piece. The answer returned is going to be a different cell reference if true. 1000 is not static. If there is a spouse, and the spouse is 65 or older, then I need a return of E9

Change the 1000 to E9.

Change the 1000 to E9.

Works beautifully!!ray: Thank you so much. Any chance you could tell me why the IF/AND didn't work the way it should have? I want to learn from my mistakes.

AND evaluates all of its arguments;

AND(C35:C45="spouse", I35:I45>64)

... would return TRUE if all of C were 'spouse" and all of I were > 64 (and if it were entered as an array formula).

Ahh, I thought it may be something like that. Thanks again.

Replies
1
Views
182
Replies
12
Views
446
Replies
2
Views
287
Replies
6
Views
474
Replies
2
Views
629

1,202,987
Messages
6,052,932
Members
444,616
Latest member
novit19089

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back