Vlookup help

[Guest]

Hi,

Trying to use the VLOOKUP function on a column that has multiple names
(comma seprated) or with % in brackets. Data originally from MSProject. I
need to lookup from the left side only, match it with a standard value and
return the standard value.

Data to be matched - Bob Smith,Client or Bob Smith [88%]
Lookup says - Bob Smith

Can someone please help me with the equation?

I've tried to keep the equation simple, also tried left, trim etc. with no
luck. Not convinced INDEX will do the whole trick so tried it in combination
with vlookup - no luck.

This is the original equation, it works fine where the value in AA18 is
exactly the same as Resources!$B.

=VLOOKUP($AA18,Resources!$B:$C,1,FALSE)

Any help appreciated

 

[Peo Sjoblom]

Try wildcards

VLOOKUP("*"&$AA18&"*",Resources!$B:$C,1,FALSE)


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Guest]

At the end of your VLookup formula change the False to True. False returns
exact matches, True returns closest possible match.

Shauna

 

[Peo Sjoblom]

TRUE returns the largest value that is less than or equal to the lookup
value, not the closest. Also for that to work you need to have the first
column of the lookup table sorted in ascending order

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

At the end of your VLookup formula change the False to True. False
returns
exact matches, True returns closest possible match.

Shauna

Hi,

Trying to use the VLOOKUP function on a column that has multiple names
(comma seprated) or with % in brackets. Data originally from MSProject.
I
need to lookup from the left side only, match it with a standard value
and
return the standard value.

Data to be matched - Bob Smith,Client or Bob Smith [88%]
Lookup says - Bob Smith

Can someone please help me with the equation?

I've tried to keep the equation simple, also tried left, trim etc. with
no
luck. Not convinced INDEX will do the whole trick so tried it in
combination
with vlookup - no luck.

This is the original equation, it works fine where the value in AA18 is
exactly the same as Resources!$B.

=VLOOKUP($AA18,Resources!$B:$C,1,FALSE)

Any help appreciated

 

[Guest]

Thanks for this Peo, it now works a treat.

TRUE returns the largest value that is less than or equal to the lookup
value, not the closest. Also for that to work you need to have the first
column of the lookup table sorted in ascending order

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

At the end of your VLookup formula change the False to True. False
returns
exact matches, True returns closest possible match.

Shauna

Hi,

Trying to use the VLOOKUP function on a column that has multiple names
(comma seprated) or with % in brackets. Data originally from MSProject.
I
need to lookup from the left side only, match it with a standard value
and
return the standard value.

Data to be matched - Bob Smith,Client or Bob Smith [88%]
Lookup says - Bob Smith

Can someone please help me with the equation?

I've tried to keep the equation simple, also tried left, trim etc. with
no
luck. Not convinced INDEX will do the whole trick so tried it in
combination
with vlookup - no luck.

This is the original equation, it works fine where the value in AA18 is
exactly the same as Resources!$B.

=VLOOKUP($AA18,Resources!$B:$C,1,FALSE)

Any help appreciated