Home |
Search |
Today's Posts |
#1
|
|||
|
|||
using wildcards in vlookup
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! |
#2
|
|||
|
|||
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" wrote in message ... 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! |
#3
|
|||
|
|||
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" wrote in message ... 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! |
#4
|
|||
|
|||
Based on your example, this may work:
=VLOOKUP(MID(A2,7,5),Sheet1!A:B,2,FALSE) In article , "Alex" 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! |
#5
|
|||
|
|||
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))) Hope it helped Ola Sandström See encl zip-file: http://www.excelforum.com/attachment...tid=3495&stc=1 +-------------------------------------------------------------------+ |Filename: Book4.zip | |Download: http://www.excelforum.com/attachment.php?postid=3495 | +-------------------------------------------------------------------+ -- olasa ------------------------------------------------------------------------ olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760 View this thread: http://www.excelforum.com/showthread...hreadid=378998 |
#6
|
|||
|
|||
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" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup & wildcards | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |