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!

任务是使用“地址”列检查我f 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 isnotchecked 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.