INDEX/MATCH to find all instances of the search string

[lost22]

I know how to do the data filter [custom/advanced filter]

But for VLOOKUP or INDEX/MATCH purposes how do I find all instances where I am doing a compare string search.

Second question for now:

If I was searching for the string "North" where I have a list of addresses that can be "123 Northshore Drive" or "1A North Avenue" what would be the index/match code for the contained string?

Thank you

 

[jdc]

Hi

Not sure exactly what you are after, this code will count the number of cells from A2:20 containing "north" in there somewhere.

=COUNTIF(A2:A20,"=*north*")

Are you trying to return a code for whether it is true or false, 1 or 0 for a match.

If you could post more details,

Regards

John

 

[just_jon]

Got a bit of sample data to work with?

 

[lost22]

vlookup or index match to find all instances of a string in

I have a list of properties [each with a text based ID #] and to each one is assigned a property code [text based ID#]. A property code may be assigned to more than one property [i.e. Property Code A000 may be the property code assigned to CC11 and CC12 because they are in the same area].

If I do a vlookup or index/match to find the # of properties and the properties themselves, my understanding of vlookup or index/match is the vlookup only finds the first instance.
I am trying to find all the next instances by using formulas, and not necessarily using a filter in the headings.

Thank you.

 

[just_jon]

See this thread -- http://www.mrexcel.com/board2/viewtopic.php?t=113746