Arbitrary Lookups with wildcard values

  • Thread starter Thread starter Zargon_osu
  • Start date Start date
Z

Zargon_osu

I apologize for posting multiple subjects - I thought two problems coul
be handled separately, but they must be solved together:

I need a function to find the row number of the second cell in a colum
that meets the general requirements described by wildcard characters.

For example, I need the row of the 2nd cell in a column that meets:
AA*10*

where * is any number of characters

I was refered to "Arbitrary Lookups" using array functions, but tha
only works if I am searching for a single static value and I can't see
to get array functions to accept the wildcard tests.

Also, finding the first entry that meets the wildcard is easy using th
MATCH command, but MATCH isnt good at finding the successive entrie
after the first one.

I can do each task individually, but not together.

Any thoughts?
Thanks a to
 
Zargon_osu said:
I need a function to find the row number of the second cell in a column
that meets the general requirements described by wildcard characters.

For example, I need the row of the 2nd cell in a column that meets:
AA*10*

where * is any number of characters
Click to expand...
....

If your range spanned a single column and were named List, you could try the
array formula

=INDEX(List,SMALL(IF(ISNUMBER(SEARCH("AA*10*",List)),
ROW(List)-CELL("Row",List)+1),2))

Replace the 2 with any other positive integer. To guard against the
possibility that there may not be a second (or Nth) instance matching your
text pattern, try the array formula

=IF(COUNTIF(List,"AA*10*")>=N,
INDEX(List,SMALL(IF(ISNUMBER(SEARCH("AA*10*",List)),
ROW(List)-CELL("Row",List)+1),N)),"")
 
Back
Top