using wildcards in vlookup

[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!

[olasa]

This is one option
=IF(ISERROR(MATCH(TRUE;ISNUMBER(FIND($D$3:$D$7;A3));0));"";INDEX($E$3:$E$7;MATCH(TRUE;ISNUMBER(FIND($D$3:$D$7;A3));0)))

See encl zip-file: http://www.excelforum.com/attachment...tid=3495&stc=1

Hope it helped
Ola Sandström


Note!
I forgot to mention...Confirm the formula by holding down Ctrl and Shift, then hit Enter (It's because it's a-so-called-Array-formula, which does several calculations in one go).
Here is a link if you want to know more: http://www.personal-computer-tutor.c...ayformulas.htm

[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)


"Alex" <[email protected]> wrote in message
news:[email protected]...
>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!
>

[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

"Alex" <[email protected]> wrote in message
news:[email protected]...
>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!
>

[JE McGimpsey]

Based on your example, this may work:

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

In article <[email protected]>,
"Alex" <[email protected]> wrote:

> 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!

[Alex]

Yes! Thanks much. It's not always the middle, so this worked great.

"N Harkawat" wrote:

> 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
>
> "Alex" <[email protected]> wrote in message
> news:[email protected]...
> >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!
> >
>
>
>