Trouble matching multiple cells

[bug7467]

Hello

I am having trouble deriving an IF statement worksheet formula for my problem. I am trying to see if my city and state matches my list in targeted cities and states. The tricky part is that both entries in my city list must match both entries in each individual row in target cities.

Any help is appreciated!

[/URL][/IMG]

 

[Eric W]

Here are a couple of options:

	A	B	C	D	E	F	G
53	Target Cities			City List		Match
54	Chico	CA		Cicero	IL	TRUE	TRUE
55	Chula Vista	CA		Cicero	CA	FALSE	FALSE
56	Cicero	IL		Cleveland	OH	TRUE	TRUE
57	Cincinnati	OH		Cleveland	NY	FALSE	FALSE
58	Clarksville	TN
59	Clearwater	FL
60	Cleveland	OH
61	Clovis	CA
62	College Station	TX
63

<tbody>
</tbody>

Sheet19​

Worksheet Formulas Cell Formula F54 =COUNTIFS($A:$A,D54,$B:$B,E54)>0 <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula G54 {=ISNUMBER(MATCH(D54&"|"&E54,$A$54:$A$62&"|"&$B$54:$B$62,0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

I'd go with the one in F54, the other is for future reference, or if you have a version of Excel prior to 2007.

 

[bug7467]

Here are a couple of options:

	A	B	C	D	E	F	G
53	Target Cities			City List		Match
54	Chico	CA		Cicero	IL	TRUE	TRUE
55	Chula Vista	CA		Cicero	CA	FALSE	FALSE
56	Cicero	IL		Cleveland	OH	TRUE	TRUE
57	Cincinnati	OH		Cleveland	NY	FALSE	FALSE
58	Clarksville	TN
59	Clearwater	FL
60	Cleveland	OH
61	Clovis	CA
62	College Station	TX
63

<tbody>
</tbody>

Sheet19​

Worksheet Formulas Cell Formula F54 =COUNTIFS($A:$A,D54,$B:$B,E54)>0 <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula G54 {=ISNUMBER(MATCH(D54&"|"&E54,$A$54:$A$62&"|"&$B$54:$B$62,0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

I'd go with the one in F54, the other is for future reference, or if you have a version of Excel prior to 2007.

Thank you very much!

 

[Eric W]

Glad to help!