SEARCH and FIND and V.LOOKUP combined

[Øyvind Granberg]

Hi...

I wonder...
I am using the v.lookup function in a cell.
But the text in the matrix and the cell with the lookup value not always are
exactly the same.
Ex; "Hull" and "Hull City" or "dumb" in "Old dumb fart"
I have found that I can use SEARCH or SEARCHB to find a word in a text
string.

How do I use SEARCH in combination that with the v.lookup function in a
formula?



--

Vennlig hilsen
Øyvind Granberg

(e-mail address removed)
www.tresfjording.com

 

[Øyvind Granberg]

Or do I use MID or MIDB?
How do I combine that one with v.lookup?

--

Vennlig hilsen
Øyvind Granberg

(e-mail address removed)
www.tresfjording.com

 

[T. Valko]

Use wildcards:

=VLOOKUP("*Hull*",F1:G5,2,0)

Will match Hull City

If the lookup_value is in a cell:

A1 = Hull

=VLOOKUP("*"&A1&"*",F1:G5,2,0)

 

[Øyvind Granberg]

Thank you, Biff
That did the trick...

I've got to learn myself some excel tricks


--øg--

--

Vennlig hilsen
Øyvind Granberg

(e-mail address removed)
www.tresfjording.com

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[SmartbizAustralia]

Be careful with vlookup as if it cannot find a match it returns a
value from another cell...and that means your figures can be out.

Index/match is better...get the vlookup working and then it's just a
minor change in the parameters..

Regards,
Tom Bizannes
Microsoft Excel Specialist
Sydney, Australia
http://www.macroview.com.au

 

[Dave Peterson]

Not using the formula that Biff suggested.

=vlookup() with the fourth parm of false or 0 will look for exact matches.

 

[Øyvind Granberg]

So I found out... Adding FALSE at the end made it return the correct
value...

--

Vennlig hilsen
Øyvind Granberg

(e-mail address removed)
www.tresfjording.com