Using IF/AND with two ranges of data.

Teamciccone30576

I have been working on the following problem for a couple of days, but I have not been able to get anything even remotely correct to work as desired. I can only get anything to execute if I tie the two lookup cells to row #.

=IF(AND(Adresses@row = Streets1, Include1 = "Yes"), "Yes","No")

That is obvious not what I want!

Hope someone can help.

The actual project has 2 sheets with many columns, but this task only uses 4 columns.

So, for testing purposes I combined all the 4 columns involved in this task into one sheet..

See the screen print!

The task is to use the “Address” column to check if that particular street is assigned to be part of a mailing list as indicated in the “Streets” and “Include” column.

It should print a “Yes or “No” in the Mailing list column.

The sheet already contains a Help Column used by another Formula – I don’t know if that is helpful for this.

Note 2:

The preferred solution would be to replace the Yes/No columns with Checkbox column, but I don’t think that is possible – I don’t see any sample how to set the checkbox based on another checkbox.

Kurt

Teamciccone30576

@Joe Calderazzo

I guess you helped me with the checkbox question as well.

I used the exact same formular on the sheet, now with Checkbox columns, and it works perfectly.

And when I change the “Include” column selection, it changes the “Mailing list" instantly.

Exactly as I wanted it to work.

 

I cannot thank you enough for getting me on the right course with this.

Joe Calderazzo

=IF(AND(Addresses@row = [Streets]@row, [Include]@row = "Yes"), "Yes", "No")

Teamciccone30576

@Joe Calderazzo

Thanks for looking at this, but this just move down the Streets column.

The sample have 3 ALICE LN's in the "Adresses" column - all 3 should be evaluated against the list of streets and should find that ALICE LN in "Adresses" column is = ALICE LN in the "Street" column and find it has a "Yes" and therefore should be included and should produce a "Yes" in the mailing List.

(I knew, I should have corrected the spelling before I posted)

Joe Calderazzo

OK, thank you for the clarification. What you are in need of is an Index/Match to return the yes/no that already exists in the Include column...

This is what that would look like:

In the the Mailing List column, enter the below

=INDEX(Include:Include, MATCH(Addresses@row, Streets:Streets))

Make this a Column Formula

If the Streets and Include columns are on separate sheet, in this case named Sheets 1, then the formula looks like this - renaming the column reference to Sheet 1_Streets and Sheet 1_Include accordingly.

=INDEX({Sheet 1_Include}, MATCH(Addresses@row, {Sheet 1_Streets}))

Let me know if this works for you and is what you are looking for.

Below are the results of both of these set as Column Formulas.

Teamciccone30576

@Joe Calderazzo

F A N T A S T I C

This work as advertised.

On the single sheet it works directly.

Changing the test sheet to a 2 Sheet solution, I was not able to use the Formula you quoted – I probably messed it up somehow.

I used the “Edit References” pop up to get the references in – and it worked Perfect.

·        =INDEX({Sheet 1 Range 1},MATCH(Addresses@row, {Sheet 1 Range 2}))

I presume the way you quoted, is just another way to do the same thing – I just did not get it right.

 I incorporated the formula in the actual project – 4,000 addresses, 300 street and 11 Mailing lists –  and it works beautiful. (Just did one Mailing list for now)

 So many thanks for this – I would never have found it by myself.

I presume we cannot use Check boxes “Checked” or “Unchecked” instead of the “Yes” or “No” column? I have not seen any samples of a checkbox been set by another check box.

Kurt

Joe Calderazzo

Yes, you can use checkboxes to check other checkboxes. What are you looking to do with the checkboxes?

Teamciccone30576

@Joe Calderazzo

In the current project, replace the "Include" and the "Mailing List" checkboxes

Kurt

Teamciccone30576

@Joe Calderazzo

I should clarify my last post!

If an “Include” check box is not checked for a street  in the “Streets” column and “Mailing List” check box is checked previous version, the formular need to uncheck that check mark.

Mailing list changes now and then.

Kurt

Teamciccone30576

@Joe Calderazzo

I guess you helped me with the checkbox question as well.

I used the exact same formular on the sheet, now with Checkbox columns, and it works perfectly.

And when I change the “Include” column selection, it changes the “Mailing list" instantly.

Exactly as I wanted it to work.

 

I cannot thank you enough for getting me on the right course with this.