Excel lookup help

[cdcsolutions]

Is there anyway a person can use the Vlookup to lookup a list by using partial information. Eg. In Cell B1 enter 8c-26-062-08 and it will find the info from Column C from a list that contains Eg. Bertsei 199/08c-26-062-08s4/02. By typing in column "B1" 8c-26-062-08, I want A1 to list "Bertsei 199/08c-26-02-08s4/02 which comes from Column C of a different sheet. My problem is that I want to type in only parts of the full description to result in the full description auto filling.

Help please if this is possible to do. I'm thinking of using Match? Thanks!

 

[FormR]

Try, for example:

=VLOOKUP("*"&B1&"*",Sheet2!C1:C1000,1,0)

 

[cdcsolutions]

Thank-you so much! I am very close to resolving this issue. It works for one entry so I need to add within the formula the lookup for a variety of info in column B. For example I will have a different number 9B-25-062-09 in b2 and I need it to find that number within Column C. I will have hundreds of numbers looking throughout Column C for the numbers within a variety of info such as Bertsei 199/08c-26-062-08s4/02 . Eg Bersei 200/09c-25/062, Western 198/10b/5/062

Therefore a lookup in hundreds of cells in column but also typing in column B a variety of numbers. Thanks! I know the formula is close.

 

[FormR]

Like this?

In A1 and copy down.
=VLOOKUP("*"&B1&"*",Sheet2!$C$1:$C$1000,1,0)

Change the 1000 to the known maximum number of rows in the other sheet.

Or A1 and copy down if the max rows is not known:
=VLOOKUP("*"&B1&"*",Sheet2!$C:$C,1,0)

 

[cdcsolutions]

Like this?

In A1 and copy down.
=VLOOKUP("*"&B1&"*",Sheet2!$C$1:$C$1000,1,0)

Change the 1000 to the known maximum number of rows in the other sheet.

Or A1 and copy down if the max rows is not known:
=VLOOKUP("*"&B1&"*",Sheet2!$C:$C,1,0)

This works perfectly! Thankyou!

 

[cdcsolutions]

This works perfectly! Thankyou!

One more small problem. If Column B is empty, I want the cell to be blank, for some reason it pulls from the linked sheet an entry which makes no sense. Any ideas why? Thanks.

 

[FormR]

If Column B is empty, I want the cell to be blank

Hi, try:

=IF(B1="","",VLOOKUP("*"&B1&"*",Sheet2!$C$1:$C$1000,1,0))

 

[mfexcel]

Just in case if you would like to know more about how the "*" works.

Advanced vlookup – wildcard characters “?” and “*” | wmfexcel

How to lookup only partially-matched value?? – Crazy lookup!!! | wmfexcel

 

[cdcsolutions]

Hi, try:

=IF(B1="","",VLOOKUP("*"&B1&"*",Sheet2!$C$1:$C$1000,1,0))

This is perfect! Thank-you so much!