Search range

  • Thread starter Thread starter johnrb7865
  • Start date Start date
J

johnrb7865

Hi, I need to search a range of cells for a piece of text, not an exact match
of the whole string, but just some words. Then, when it finds that I need to
pull the information same row, 1 column to the left. I have descriptions in
the 2nd column and codes in the 1st, and when it finds a piece of the
description I need the code.

Any help would be great.

Thanks,
John
 
...........A..........B
1........5........not his stuff
2........2........some more junk
3........1........yes, this is it
4........6........blah blah blah
5........9........I need a break

Lookup "this is it"

D1 = this is it

=INDEX(A1:A5,MATCH("*"&D1&"*",B1:B,0))

Result = 1
 
Yes, I know I could've used SUMIF but I'm working on the assumption the
value to be returned could be either numeric or text.
 
That worked, thank you. Can you explain to me how the section "*"&D1&"*"
works? What are the "*" and the & for?

Thanks,
John
 
1 more quick one. Is there a way to tell it that once it found the text, look
for the next instance? There could be more than 1 match and I need each of
the corresponding numbers placed in a column.

Thanks,
John
 
What are the "*" and the & for?

The "*" are wildcards and the "&" is the concatenation operator. When you
see the "&" think of it as the word AND.

This is what it means:

"any character or characters" & this is it & "any character or characters"
 
If there's more than 1 instance then it gets complicated. I'm getting ready
to break for dinner so I'll be away for a few hours but I'll post a solution
when I return (unless someone else chimes in while I'm gone).
 
Ok, based the table below:

...........A..........B
1........5........not his stuff
2........2........some more junk
3........1........yes, this is it
4........6........blah blah blah
5........9........this is it

Lookup "this is it"

D1 = this is it

Enter this array formula** in E1 and copy down until you get blanks:

=IF(ROWS(E$1:E1)<=COUNTIF(B$1:B$5,"*"&D$1&"*"),INDEX(A$1:A$5,SMALL(IF(ISNUMBER(SEARCH(D$1,B$1:B$5)),ROW(A$1:A$5)),ROWS(E$1:E1))-ROW(A$1)+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Results:
E1 = 1
E2 = 9
E3 = (blank)
 
OK, so I got it to work with your data, but when I tried to adapt it to mine
I got an error, #Value!. I am using named ranges on a different worksheet,
same workbook, for my equivalent of your A1:A5 and B1:B5, would that make a
difference?

Thanks for all your help. I'm sorry this is becoming more complicated than
you probably wanted to deal with.

Thanks,
John
 
Named ranges don't make any difference *except* that they need to be the
*exact same size*.
 
Back
Top