Search range
 

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

Search range
 

...........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

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
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

Search range
 

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
..........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

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
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

Search range
 

That worked, thank you. Can you explain to me how the section "*"&D1&"*"
works? What are the "*" and the & for?

Thanks,
John

"T. Valko" wrote:

...........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

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
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

Search range
 

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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
..........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

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
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

Search range
 

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"

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
That worked, thank you. Can you explain to me how the section "*"&D1&"*"
works? What are the "*" and the & for?

Thanks,
John

"T. Valko" wrote:

...........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

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
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

Search range
 

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).

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
..........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

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
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

Search range
 

Thanks, I really appreciate it.

Have a good dinner!!

Thanks,
John

"T. Valko" wrote:

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).

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
..........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

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
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

Search range
 

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&"*"),IND EX(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)


--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
Thanks, I really appreciate it.

Have a good dinner!!

Thanks,
John

"T. Valko" wrote:

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).

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
..........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

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
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

Search range
 

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

"T. Valko" wrote:

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&"*"),IND EX(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)


--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
Thanks, I really appreciate it.

Have a good dinner!!

Thanks,
John

"T. Valko" wrote:

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).

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
..........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

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
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

Search range
 

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


--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
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

"T. Valko" wrote:

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&"*"),IND EX(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)


--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
Thanks, I really appreciate it.

Have a good dinner!!

Thanks,
John

"T. Valko" wrote:

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).

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in message
...
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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
..........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

--
Biff
Microsoft Excel MVP


"johnrb7865" wrote in
message
...
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