vlookup

[dwddavid]

Column C contains ID numbers
Column B Contain a sentence of information
Column A a word
I want a vlookup formula to return the ID number in Column C for any sentence in column B that contains the word that is in a cell in column A. I only know the formula if the cell in Column A = the content of the cell of Column B.

 

[Nalani]

Welcome to the Board

To better help us to help you, could you provide a sample of what is in your row of iterest for Columns A, B, and C ?

And since you said you know a particular formula. Could you also provide that and what cell you are inputting it.

 

[barry houdini]

You could use a VLOOKUP with wildcards, e.g.

=VLOOKUP("*"&A1&"*",B:C,2,0)

 

[Aladin Akyurek]

Column C contains ID numbers
Column B Contain a sentence of information
Column A a word
I want a vlookup formula to return the ID number in Column C for any sentence in column B that contains the word that is in a cell in column A. I only know the formula if the cell in Column A = the content of the cell of Column B.

Does the data consist of a range in B:C or in A:C? If former, does that mean that you have look up values in A (words), right? If so, does something like:

=INDEX(C:C,MATCH("*A2&"*",B:B,0))

works?

 

[dwddavid]

Sorry. I misposted my question.
Column C has my ID numbers
C2 10
C3 12
C4 14

Column B has my sentences
B2 I am handsome
B3 This is a test
B4 My name is David

Column F is my look up value
F2 I am Handsome
F3 This is a test
F4 My name is David

If in Column G, I type =VLOOKUP(F2,$B$2:$C$4,2,FALSE) the value that is returned is 10.

Now suppose F2 only contains the word Handsome. I still want 10 to be returned in Column G. I am getting a #N/A with the above formula.

 

[barry houdini]

My suggestion was to use wildcards, so with that formula....

=VLOOKUP("*"&F2&"*",$B$2:$C$4,2,FALSE)

 

[Aladin Akyurek]

Sorry. I misposted my question.
Column C has my ID numbers
C2 10
C3 12
C4 14

Column B has my sentences
B2 I am handsome
B3 This is a test
B4 My name is David

Column F is my look up value
F2 I am Handsome
F3 This is a test
F4 My name is David

If in Column G, I type =VLOOKUP(F2,$B$2:$C$4,2,FALSE) the value that is returned is 10.

Now suppose F2 only contains the word Handsome. I still want 10 to be returned in Column G. I am getting a #N/A with the above formula.

In order to rectify a typo in my suggestion...

=INDEX(C:C,MATCH("*"&F2&"*",B:B,0))

 

[dwddavid]

Thanks guys. That saves me a lot of trouble. It worked.

 

[dwddavid]

Similar Question:
Column T, I have values T1 =1 T2 =2, T3=3 and so on
Column S, I have a string of fruit: S1 = Oranges,Grapes,Bananas S2 = Apples,Grapefruit, S3 = Cherries,Tomatos

Notice there is no spaces between the comma and fruit name.

In Column G I have sentences for example:
G1 = I like Cherries

In M1, I have got the formula =vlookup("*"&G1&"*",$S$1:$T$40,2,FALSE)

Since a word (in this case Cherries) is listed in G1 and S3, I would like the value in M1 returned as 3 which is the value in T3.

What do I need to change in the formula?

 

[Aladin Akyurek]

Similar Question:
Column T, I have values T1 =1 T2 =2, T3=3 and so on
Column S, I have a string of fruit: S1 = Oranges,Grapes,Bananas S2 = Apples,Grapefruit, S3 = Cherries,Tomatos

Notice there is no spaces between the comma and fruit name.

In Column G I have sentences for example:
G1 = I like Cherries

In M1, I have got the formula =vlookup("*"&G1&"*",$S$1:$T$40,2,FALSE)

Since a word (in this case Cherries) is listed in G1 and S3, I would like the value in M1 returned as 3 which is the value in T3.

What do I need to change in the formula?

Create a range, say A2:A8, on a separate sheet housing the following individual items and
name this range List using the Name Box on the Formula Bar:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>Apples</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Bananas</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Cherries</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Grapefruit</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Grapes</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Oranges</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>Tomatos</TD></TR></TBODY></TABLE>

Now enter in M1...

=INDEX(T1:T3,MATCH("*"&LOOKUP(9.99999999999999E+307,SEARCH(List,G1),List)&"*",S1:S3,0))