Vlookup with part match
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 |
Vlookup with part match
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 |
Vlookup with part match
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 |
Vlookup with part match
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 . |
Vlookup with part match
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 . |
Vlookup with part match
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 |
All times are GMT +1. The time now is 04:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com