using wildcards in vlookup

A

Alex

I need help with the a vlookup formula that needs to use wildcards.

Sheet 1 has the following columns:

MSO# ORDER
NQCT8 111
NQCT8 222
NQFH4 333
NQFJ6 444
NQXF0 555

Sheet 2 has the following columns:

TRKCASE ORDER
JVB65/NQXFO/Z1R09148 555
JVB65/NQCT8/Z1K05629 222
JVB65/NSTB4/Z1R09145
JVB65/NTDZ9/84EX00053
JVB65/NTZS1/Z1K05424

I need a vlookup function in the ORDER column in Sheet 2 that will look to
see if the TRKCASE in the same line matches any of the MSO#'s in Sheet1 and
if so, enter the ORDER # from Sheet 1. What's confusing me is that the MSO#
from Sheet 1 will be inside TRKCASE. I've included what ORDER #'s should end
up in the ORDER column in Sheet 2.

I appreciate the help. Thank you!
 
P

Peo Sjoblom

if it is always the middle part NQetc then you could use

=VLOOKUP(MID(A1,FIND("/",A1)+1,5),Sheet2!$A$2:$B$100,2,0)

--
Regards,

Peo Sjoblom

(No private emails please)
 
N

N Harkawat

Name a range "mso" for the range in sheet 1 for the mso # say from A1:a1000

then on sheet 2 cell B1 enter this formula and copy it down
=INDEX(Sheet1!$B$1:$B$1000,MATCH(1,COUNTIF(A1,"*"&mso&"*"),0))
array entered (ctrl+shift+enter)

where B1:b1000 on sheet 1 are the order
and your trkcase is on sheet2 from A1 downwards
 
J

JE McGimpsey

Based on your example, this may work:

=VLOOKUP(MID(A2,7,5),Sheet1!A:B,2,FALSE)
 
O

olasa

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top