Hi,

I'm looking up the contents of cell A1 against a list in D1:F50, however,
cell A1 contains for example Z1234 whereas the list in D1:F50 has Z1234 - Mr
R J Smith. I could insert an intermediate column and use =LEFT(D1,5) to
extract the data to find a match on but this then effects other worksheets
that link in.

Is there a way to modify the belwo formula to achive my requirement.

=VLOOKUP(A1,$D$1:$F$50,2,0)

Thanks, Rob

Try it like this:

=VLOOKUP("*"&A1&"*",$D$1:$F$50,2,0)

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Hi,

I'm looking up the contents of cell A1 against a list in D1:F50, however,
cell A1 contains for example Z1234 whereas the list in D1:F50 has Z1234 -
Mr R J Smith. I could insert an intermediate column and use =LEFT(D1,5)
to extract the data to find a match on but this then effects other
worksheets that link in.

Is there a way to modify the belwo formula to achive my requirement.

=VLOOKUP(A1,$D$1:$F$50,2,0)

Thanks, Rob

Brillant, works a treat. Thanks very much. Rob

"T. Valko" wrote in message
...
Try it like this:

=VLOOKUP("*"&A1&"*",$D$1:$F$50,2,0)

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Hi,

I'm looking up the contents of cell A1 against a list in D1:F50, however,
cell A1 contains for example Z1234 whereas the list in D1:F50 has Z1234 -
Mr R J Smith. I could insert an intermediate column and use =LEFT(D1,5)
to extract the data to find a match on but this then effects other
worksheets that link in.

Is there a way to modify the belwo formula to achive my requirement.

=VLOOKUP(A1,$D$1:$F$50,2,0)

Thanks, Rob

Here's one more for ya!
=VLOOKUP(SUBSTITUTE(A1," ","*"),$A1:$B3,2,FALSE)

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Rob" wrote:

Brillant, works a treat. Thanks very much. Rob

"T. Valko" wrote in message
...
Try it like this:

=VLOOKUP("*"&A1&"*",$D$1:$F$50,2,0)

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Hi,

I'm looking up the contents of cell A1 against a list in D1:F50, however,
cell A1 contains for example Z1234 whereas the list in D1:F50 has Z1234 -
Mr R J Smith. I could insert an intermediate column and use =LEFT(D1,5)
to extract the data to find a match on but this then effects other
worksheets that link in.

Is there a way to modify the belwo formula to achive my requirement.

=VLOOKUP(A1,$D$1:$F$50,2,0)

Thanks, Rob





.

Thanks Ryan, not quite what I wanted but a good example of using Substitute
to replace a space with *.

Regards, Rob

"ryguy7272" wrote in message
...
Here's one more for ya!
=VLOOKUP(SUBSTITUTE(A1," ","*"),$A1:$B3,2,FALSE)

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Rob" wrote:

Brillant, works a treat. Thanks very much. Rob

"T. Valko" wrote in message
...
Try it like this:

=VLOOKUP("*"&A1&"*",$D$1:$F$50,2,0)

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Hi,

I'm looking up the contents of cell A1 against a list in D1:F50,
however,
cell A1 contains for example Z1234 whereas the list in D1:F50 has
Z1234 -
Mr R J Smith. I could insert an intermediate column and use
=LEFT(D1,5)
to extract the data to find a match on but this then effects other
worksheets that link in.

Is there a way to modify the belwo formula to achive my requirement.

=VLOOKUP(A1,$D$1:$F$50,2,0)

Thanks, Rob





.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Brillant, works a treat. Thanks very much. Rob

"T. Valko" wrote in message
...
Try it like this:

=VLOOKUP("*"&A1&"*",$D$1:$F$50,2,0)

--
Biff
Microsoft Excel MVP


"Rob" wrote in message
...
Hi,

I'm looking up the contents of cell A1 against a list in D1:F50,
however, cell A1 contains for example Z1234 whereas the list in D1:F50
has Z1234 - Mr R J Smith. I could insert an intermediate column and use
=LEFT(D1,5) to extract the data to find a match on but this then effects
other worksheets that link in.

Is there a way to modify the belwo formula to achive my requirement.

=VLOOKUP(A1,$D$1:$F$50,2,0)

Thanks, Rob