Function for determining if a city is contained in a list

[Leila-Marie]

Hi there...

I am currently trying to work out how I can get Excel to help me check some lists...

I have a long list of addresses (say 4000) and I want it checked against a shorter list of 230 cities that are of interest to me (well, to my boss...)

So, so far all I've managed to do is do a really long combination of "OR" functions, which I've stringed together (see below) but I can only get to about 85 cities, before Excel tells me my function is too long...

So, this (dodgy) function allows me to check whether the city of the address (the city is in cell C1) corresponds to the first 85 cities in column F)

While this does give me the desired results of TRUE or FALSE, it's very unelegant for one thing, and - what is far worse - limits me to checking lists no longer than approx. 85 entries.

There MUST be a better way to do this, surely?

I'm not all that bad at using Excel, but I've searched for 4 hours now and cannot find ANY way (that I can understand) that lets me solve my task...

Many thanks for any pointers or suggestions...

(This may have been covered in previous posts, but the posts I found that sounded SORT OF similar, I couldn't understand - and I didn't know how to refine my search any better - so sorry if this question has been posted before)

Thank you!

Leila-Marie





<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:HyphenationZone>21</w:HyphenationZone> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]-->=OR(OR(C1=$F$1;C1=$F$2;C1=$F$3;C1=$F$4;C1=$F$5;C1=$F$6;C1=$F$7;C1=$F$8;C1=$F$9;C1=$F$10;C1=$F$11;C1=$F$12;C1=$F$13;C1=$F$14;C1=$F$15;C1=$F$16;C1=$F$17;C1=$F$18;C1=$F$19;C1=$F$20);OR(C1=$F$21;C1=$F$22;C1=$F$23;C1=$F$24;C1=$F$25;C1=$F$26;C1=$F$27;C1=$F$28;C1=$F$29;C1=$F$30;C1=$F$31;C1=$F$32;C1=$F$33;C1=$F$34;C1=$F$35;C1=$F$36;C1=$F$37;C1=$F$38;C1=$F$39;C1=$F$40);OR(C1=$F$41;C1=$F$42;C1=$F$43;C1=$F$44;C1=$F$45;C1=$F$46;C1=$F$47;C1=$F$48;C1=$F$49;C1=$F$50;C1=$F$51;C1=$F$52;C1=$F$53;C1=$F$54;C1=$F$55;C1=$F$56;C1=$F$57;C1=$F$58;C1=$F$59;C1=$F$60);OR(C1=$F$61;C1=$F$62;C1=$F$63;C1=$F$64;C1=$F$65;C1=$F$66;C1=$F$67;C1=$F$68;C1=$F$69;C1=$F$70;C1=$F$71;C1=$F$72;C1=$F$73;C1=$F$74;C1=$F$75;C1=$F$76;C1=$F$77;C1=$F$78;C1=$F$79;C1=$F$80;C1=$F$81;C1=$F$82;C1=$F$83;C1=$F$84;C1=$F$85))<!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Normale Tabelle"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

 

[HalfAce]

I don't think I understand, because what I'm thinking would seem too simple compared with what you've already attempted.
Are you just trying to see if the city in C1 is in your list of cities in (for example) column F?

If that's all then you should be able to get away with something as easy as: =COUNTIF(F:F,C1)

But you also said you're "not all that bad at using Excel" so I have to imagine you've already thought of this or the problem is more complex than I'm getting from reading your post.

 

[Smitty]

Welcome to the Board!

You could give MATCH a shot.

=MATCH(C1,F1:F5,0)

Which will return the position of a matched item in the list, or:

=IF(ISNUMBER(MATCH(C1,F1:F5,0)),TRUE,FALSE)

Which will return True/False.

HTH,

EDIT: Heya Dan! Long time...

 

[AhoyNC]

Welcome to the forum.

You may find this link to be of help.

Slaying Excel Dragons Book #29: MATCH Function To Compare Two Lists, Tables or Databases - YouTube

Also, if you search this site Excel is Fun Channel on You Tube you will find a number of videos on how to compare lists.

 

[HalfAce]

Hey Smitty!

Yeah, they don't let me out much anymore...

 

[markmzz]

Another way:

=SUMPRODUCT(-(F$1:F$240=C1))<0

Markmzz

 

[Leila-Marie]

I don't think I understand, because what I'm thinking would seem too simple compared with what you've already attempted.
Are you just trying to see if the city in C1 is in your list of cities in (for example) column F?

If that's all then you should be able to get away with something as easy as: =COUNTIF(F:F,C1)

But you also said you're "not all that bad at using Excel" so I have to imagine you've already thought of this or the problem is more complex than I'm getting from reading your post.

Thank you....!!!!!

I guess "not all that bad" is relative, huh? What I meant is that I use it regularly and it's rare that I can't get it to do what I want...

I've been trying out different functions, non of which were working... but that does (how embarassing!)

My boss is going to be so happy - she is so stressed out currently (starting up a new line of business) and this will be one less worry on her mind... I will be sure to tell her I got the help here on the forum tho - I won't pretend it was MY excel skills that solved this problem!

XXX

Leila-Marie

 

[Leila-Marie]

Welcome to the Board!

You could give MATCH a shot.

=MATCH(C1,F1:F5,0)

Which will return the position of a matched item in the list, or:

=IF(ISNUMBER(MATCH(C1,F1:F5,0)),TRUE,FALSE)

Which will return True/False.

HTH,

EDIT: Heya Dan! Long time...

Hi there! Thank you - I will give this a try too - I was trying "match" earlier but had no luck with it, but always looking to learn something new. XXX Leila-Marie

 

[Leila-Marie]

Many thanks to all for the quick suggestions... Now wishing I hadn't spent a whole 4 hours trying to solve this on my own, before searching for an Excel Forum (which I didn't even realise existed, before today!)

You have made my day (which was filled with work and stress so far) - I love the internet and wonderful people who post helpful stuff. (I contribute on other boards/ forums - so the karma is all balanced... )


Thank you!!!!!!

 

[HalfAce]

You're most welcome. Glad it helped.

I won't pretend it was MY excel skills that solved this problem!

Well, it WAS your excel skills that 'found' a solution to the problem, so you should get some credit for that...