Vlookups, with full search and wildcard and text,

[novice14]

Hello,

Can someone please explain how I can do a Vlookup..(not partially) but in a dataset/range and search in full all the way down the column and then bring back anything it contains as follows...

e.g.

Column 1- contains...(the lookup column)
xxxAndy.ped..xxxx
xxxAndro...2wedvvv
xxxAndyPandy.123cvv
xxxAndwhat..so..
sdwrogdf,gkgfg
xxx234ghhd
scj2rdawnt

Column 2 (the matching column) .. contains e.g.

sd;f;g;z;;A123
vfjk45950Andypost
soAnd
Andropandro

column 3 where I will do the lookup

so I want to bring back anything that contains " And"
I have made up the items..

I know its not a wildcard lookup which is like (*"*) I think may have forgotten this though.

Secondly...I have seen two datasets e.g. one in numbers one downloaded from e.g. Oracle which is in text...
(but I do not want to click on the green tiny corner flag of the cell and convert to number..)
How can I do a vlookup irrespective what 2 datasets are...
I believe one has to use the following criteria... (--)....but couldn't see any examples..

Would appreciate a couple of examples please.

Many Thanks

 

[Somendra Misra]

Hi novice14,

Consider uploading a sample file with sample data and desired results.

Regards,

 

[novice14]

Hello sample file attached.

 

[NARAYANK991]

Hi ,

See if this formula is what you are looking for :

=IF(ISNUMBER(SEARCH("and",C3))*ISNUMBER(SEARCH("and",E3)),"And","")

As far as your second question is concerned , converting from text to numeric value is done by adding 0 ; thus , if you have a number 6 in text form in cell A1 , then in B1 if you have the formula =A1+0 , you will get the numeric value 6 in cell B1.

Converting from numeric value to text is done by concatenating a null string ; thus if you have a numeric value 6 in cell A1 , then in B1 if you have the formula =A1&"" , you will get the text string 6 in cell B1.

Narayan