How to search for a specific text within a cell and return the value of neighboring cell

[sklinke]

Hi,

I have a small problem:
I would like to search for a specific text string within different cells that contains a lot of text and return the value of the cell next to the matching cell.

E.G.
In the example below I am looking for the cell containing the string "Blue" (A1) , which is B3, and I would like the formula to return "3" (C3)

Could someone help me?
Thanks in advance!



A1=Blue B1=Black, White C1=1
B2=Yellow, Orange Braun C2=2
B3=Green, Blue Violet C3=3
B4=Red, Pink C4=4

 

[T. Valko]

Hi,

I have a small problem:
I would like to search for a specific text string within different cells that contains a lot of text and return the value of the cell next to the matching cell.

E.G.
In the example below I am looking for the cell containing the string "Blue" (A1) , which is B3, and I would like the formula to return "3" (C3)

Could someone help me?
Thanks in advance!



A1=Blue B1=Black, White C1=1
B2=Yellow, Orange Braun C2=2
B3=Green, Blue Violet C3=3
B4=Red, Pink C4=4

Maybe this...

Book1
	A	B	C
1	Blue	Black, White	1
2	3	Yellow, Orange Braun	2
3	_	Green, Blue Violet	3
4	_	Red, Pink	4
Sheet1

Formula entered in A2:

=INDEX(C1:C4,MATCH("*"&A1&"*",B1:B4,0))

 

[barry houdini]

For your example you could also use VLOOKUP, i.e.

=VLOOKUP("*"&A1&"*",B1:C4,2,0)

 

[sklinke]

Thank you so much, both solutions work!
Actually it was the "*"&A1&"*" syntax that I didn't find in my book...

Thanks again!

 

[T. Valko]

Thank you so much, both solutions work!
Actually it was the "*"&A1&"*" syntax that I didn't find in my book...

Thanks again!

You're welcome. Thanks for the feedback!

 

[berenger]

Biff,

Would your vlookup formula work also on my formula? I am having an issue with this one not grabbing the information. The only result that I am getting is the Error code No Such Product Found.

Private Sub UPCNum_Change()
If Len(UPCNum.Text) = 14 Then
On Error Resume Next
x = Application.WorksheetFunction. _
VLookup(CDbl(UPCNum.Text), Worksheets("Data").Range("A2:E50000"), 2, False)
If Err.Number = 0 Then
Description = x
Else
Description = "No such product found"
End If
On Error GoTo 0
End If
End Sub