wildcard characters, can they be used in tables to be searched?
 

Hello!
I have several tables of data that I am returning into a long ten digit
number using V-lookup. The data typed in can vary, and thus so does the
resultant 10 digit number. I would like to return specific comments in
another vlookup equation based on that ten digit number.
The comments however, do not depend on all ten digits, only a few. So I
made another table in Excel that includes all my possible comments and the
number combinations that would result in that comment returning true. The
rest of the digits I filled in the ?. Now, when the vlookup, looks at the 10
digit number that results and searches for it in my new table, is there
anyway for it to accept the question marks as matching characters much as it
would when I type the question mark into the v-lookup formula itself?

wildcard characters, can they be used in tables to be searched?
 

You can use something like is array formula:

=offset( $D$1, Match( true, isnumber( find( A1, $C$1:$C$10 ) ), 0 )
-1, 0 )
{control-shift-enter}

A1 is the value you want to lookup (i.e. "abc")
C1:C10 is the lookup range (i.e. "abc???????", "bcd???????", "def???????",
....)
D1 is the top of the description/return range

Regards,
Bill


"AJL" wrote:

Hello!
I have several tables of data that I am returning into a long ten digit
number using V-lookup. The data typed in can vary, and thus so does the
resultant 10 digit number. I would like to return specific comments in
another vlookup equation based on that ten digit number.
The comments however, do not depend on all ten digits, only a few. So I
made another table in Excel that includes all my possible comments and the
number combinations that would result in that comment returning true. The
rest of the digits I filled in the ?. Now, when the vlookup, looks at the 10
digit number that results and searches for it in my new table, is there
anyway for it to accept the question marks as matching characters much as it
would when I type the question mark into the v-lookup formula itself?

wildcard characters, can they be used in tables to be searched?
 

Hi AJL,

If I understood it correct. Say for example you have the data as below
-

Col1 Col2 Col3
B100002001 12 35
B100002002 53 86
B100002003 85 25
B100002004 65 74

As you said there is 10 digit number but if you could use it with a few
numbers only with the wild card, so you can use it this way -

=VLOOKUP("*2003",A1:C4,3,FALSE)

Hope this works fine with you.


Thanks,

Shail



AJL wrote:
Hello!
I have several tables of data that I am returning into a long ten digit
number using V-lookup. The data typed in can vary, and thus so does the
resultant 10 digit number. I would like to return specific comments in
another vlookup equation based on that ten digit number.
The comments however, do not depend on all ten digits, only a few. So I
made another table in Excel that includes all my possible comments and the
number combinations that would result in that comment returning true. The
rest of the digits I filled in the ?. Now, when the vlookup, looks at the 10
digit number that results and searches for it in my new table, is there
anyway for it to accept the question marks as matching characters much as it
would when I type the question mark into the v-lookup formula itself?