Vlookup

[Benoit]

Hi,

I would like to know if it is possible to do a Vlookup on a cell where we
find text.

For example, if I have in a cell ''January 27th, 2005, airplane, J.
Hodgson''

Is-it possible to do a Vlookup to find only Hodgson? I know that I have
to do a list with Hodgson inside to be able to find Hodgson but the
problem is to find it whn we have something else in the cell.

Thank you!!!

 

[Guest]

Maybe some variation of this...........

=IF(RIGHT(A1,7)="Hodgson",VLOOKUP(RIGHT(A1,7),YOURLOOKUPTABLE,2,FALSE),"")

All on one line, watchout for wordwrap

Vaya con Dios,
Chuck, CABGx3

 

[Pete_UK]

Chuck,

I think "Hodgson" would be entered into another cell (let's say F1), so
a more general formula would be:


=IF(RIGHT(A1,LEN(F1))=F1,VLOOKUP(RIGHT(A1,LEN(F1)),your_lookup_table,2,FALSE),"")

where A1 contains ''January 27th, 2005, airplane, J. Hodgson'', for
example.

Hope this helps.

Pete

 

[L. Howard Kittle]

Hi Benoit,

You have a couple solutions, but if you want to "get" the word from anywhere
in the cell then try this.

=VLOOKUP("*"&A1&"*",C14,2,0)

Where you would enter Hodgson in A1.
And it will find Hodgson in all these examples:

January 27th, 2005, airplane, J. Hodgson
January Hodgson 27th, 2005, airplane, J.
January 27th, 2005, Hodgson airplane, J.
January 27th, Hodgson 2005, airplane, J.

It will also find "7th", "gson", "plane" and such if entered in A1, I think
you get the picture.

HTH
Regards,
Howard