Help using wildcards in VLOOKUP

[cr731]

Hi

I am trying to use wildcards in the table array of a vlookup but I am not having success. Is this possible? I want do the following:

Lookup value: 45123

Table:

Col 1 Col 2
??123 56
??124 57

So... I would want my vlookup to search for 45123, and I would think that ??123 would be a match, since ?? is the wildcard, and then return 56. Am I not understanding how wildcards are used? Can this not be done?

Thanks

 

[Aladin Akyurek]

This seems to work! Do you think you could share how this works? The part I'm a little confused by is the Match/Search part.

I assumed that we are comparing strings of length 5 with strings of length 5.
SEARCH will yield a fit at position 1 when a search string (from A2:A7) matches the target string in E2. Such an evaluation would look like this:

{#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!}

which means that the 5th string matches E2 (with ? representing any digit
at corresponding position).

MATCH with 1 as look up value wihile match-type set to 0 will
match 1 at the 5th position in the foregoing array of evaluations.
This number fed to INDEX leads us to the 5th item in B2:B7.

Hope this helps.

 

[cr731]

Thanks! Out of curiosity, would this be possible if the table being looked up was formatted like this:

45
??123
45?23

Meaning that row 1, 45, would represent 45??? Such that if the length was less than 5, the remaining characters are wildcards? As another example, a line with 4 would represent 4???? so that any 5-digit lookup starting with 4 would match that?

Hopefully that question makes sense. I guess it would be simple enough to build a few extra columns in so that you can turn the 45 into 45??? (by testing the length, etc.), but I was wondering if that step might be able to be eliminated.

 

[cr731]

As further clarification, the lookup value (the input value) would always be 5 characters. But what if the table values to match are not necessarily 5 characters? (As in my previous post, they could be less than 5 characters; however they would never be greater than 5 characters).

 

[Haseeb Avarakkan]

Hello, try,

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">45124</td><td style="text-align: right;;">45</td><td style="text-align: right;;"></td><td style="text-align: right;;">45934</td><td style="text-align: right;;">49</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">??123</td><td style="text-align: right;;">46</td><td style="text-align: right;;"></td><td style="text-align: right;;">45123</td><td style="text-align: right;;">46</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">45125</td><td style="text-align: right;;">47</td><td style="text-align: right;;"></td><td style="text-align: right;;">56789</td><td style="text-align: right;;">33</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">45123</td><td style="text-align: right;;">48</td><td style="text-align: right;;"></td><td style="text-align: right;;">49876</td><td style="text-align: right;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">45?34</td><td style="text-align: right;;">49</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">47?6?</td><td style="text-align: right;;">25</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">4</td><td style="text-align: right;;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">56</td><td style="text-align: right;;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">498</td><td style="text-align: right;;">44</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet6</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">{=INDEX(<font color="Blue">$B$2:$B$10,MATCH(<font color="Red">1,SEARCH(<font color="Green">LEFT(<font color="Purple">$A$2:$A$10&"????",5</font>),E2</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
If the single number, let's say 4 is above the other numbers starts with same number, formula will stop at the first instances.

See the result on F5. E5 starts with 4 & there is a single character 4 in A8 before 498 in A10.

 

[Aladin Akyurek]

Thanks! Out of curiosity, would this be possible if the table being looked up was formatted like this:

45
??123
45?23

Meaning that row 1, 45, would represent 45??? Such that if the length was less than 5, the remaining characters are wildcards? As another example, a line with 4 would represent 4???? so that any 5-digit lookup starting with 4 would match that?

Hopefully that question makes sense. I guess it would be simple enough to build a few extra columns in so that you can turn the 45 into 45??? (by testing the length, etc.), but I was wondering if that step might be able to be eliminated.

As further clarification, the lookup value (the input value) would always be 5 characters. But what if the table values to match are not necessarily 5 characters? (As in my previous post, they could be less than 5 characters; however they would never be greater than 5 characters).

Since you seem to stipulate that the entries whose length are less than 5 can be filled to the right with the ? wildcard, a small modification to the SEARCH bit of the formula that we have would suffice (a suggestion Haseeb Avarakkan already forwarded)...

SEARCH(LEFT($A$2:$A$10&"????",5)

giving us:

=INDEX($B$2:$B$10,MATCH(1,SEARCH(LEFT($A$2:$A$10&"????",5),E2),0))

Also worth experimenting with:

=INDEX($B$2:$B$10,MATCH(1,SEARCH($A$2:$A$10&"*"),E2),0))

which eliminates the need for an additional function call

 

[cr731]

Thank you all so much for the help! This works perfectly.

 

[Aladin Akyurek]

Thank you all so much for the help! This works perfectly.

You are welcome. Thanks for providing feedback.

 

[cr731]

[TABLE="width: 288"]
<colgroup><col width="64" style="width: 48pt;" span="6"><tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]45124[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]45[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]45934[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]49[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]??123[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]46[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]45123[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]46[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]45125[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]47[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]47260[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]45123[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]48[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]45?34[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]49[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]47?6?[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]25[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


A2:B7 houses the sample you provided (with 1 record augmented).

E2:E4 houses the target strings to look up (evaluate).

F2, control+shift+enter, not just enter, and copy down:

=INDEX($B$2:$B$7,MATCH(1,SEARCH($A$2:$A$7,E2),0))

This is bringing back a really old thread but... do you think it's possible to modify this so that it is not an array/require Ctrl-Shift-Enter? I share my workbooks with many non-advanced Excel users and can't tell you how many times they accidentally go into a formula and just hit Enter instead of Ctrl-Shift-Enter, thereby messing up the results.

 

[Aladin Akyurek]

This is bringing back a really old thread but... do you think it's possible to modify this so that it is not an array/require Ctrl-Shift-Enter? I share my workbooks with many non-advanced Excel users and can't tell you how many times they accidentally go into a formula and just hit Enter instead of Ctrl-Shift-Enter, thereby messing up the results.

Like this:

=INDEX($B$2:$B$7,MATCH(1,INDEX(SEARCH($A$2:$A$7,E2),0),0))

 

[cr731]

Like this:

=INDEX($B$2:$B$7,MATCH(1,INDEX(SEARCH($A$2:$A$7,E2),0),0))

Thanks again! I would never have thought to use INDEX that way.