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

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


"Janet BN" wrote in message
...
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

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

Shauna

"Janet BN" wrote:

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

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


"shaunap" wrote in message
...
At the end of your VLookup formula change the False to True. False
returns
exact matches, True returns closest possible match.

Shauna

"Janet BN" wrote:

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

Thanks for this Peo, it now works a treat.


"Peo Sjoblom" wrote:

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


"shaunap" wrote in message
...
At the end of your VLookup formula change the False to True. False
returns
exact matches, True returns closest possible match.

Shauna

"Janet BN" wrote:

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