Vlookup help

[Janet BN]

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


"Janet BN" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

[shaunap]

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
>
>
>

[shaunap]

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
>
>
>

[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


"shaunap" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>
>>

[Janet BN]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >>
> >>
> >>
>
>
>

[Janet BN]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >>
> >>
> >>
>
>
>