I couldn't quite find something on the net to modify that will work. I have used Lookup before but now I believe I need to use VLookup or a combination of formulas to achieve this task.

In Sheet1 I want a formula to search P901 (or 901, whatever is easier) in Sheet2 range $A$2:$A$501 and then output the cell in the next column B.

The range of cells that we will search will have string of text that look like these: G10L85P500 all the way to G10L85P999. The lookup or search will only find one.

Thanks,

Bob

• Moved by Lanlan HuangMicrosoft contingent staff 5 hours 12 minutes ago

Hello Bob:

From your post, it looks like you want to use formulas and not VBA... correct?

Yes. I'm a novice at excel and even more at VBA.

Bob:

I will have to defer this issue to another expert.  My specialty is VBA, and especially VLookups using VBA because I even wrote a book on the subject :)

Power Up Using Excel VBA Sorts and Searches

Regards,

Hi

Try this:

=IF(B2="","",VLOOKUP("*"&B2,Sheet2!A2:B102,2,FALSE))

Change B2 in the formula in sheet1 for looking up say 905  or P905 and place the formula in any cell on Sheet1.

HTH

Hi

This is the forum to discuss questions about Microsoft Excel develop (VBA). For you are intend to use formula, I will move the thread to the TechNet forum for Excel.

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

Thank you for your understanding.

Best Regards,

Lan