vlookup with not exactly matching numbers
 

I need to often match up columns on two spreadsheets. One of the xls has a
reference number of 12 digits...the other has 10 digit. The 10 digits are
the same as the 12 except they are missing the first and last number.

Is there a way to do a vlookup using a "wild" card type feature?

vlookup with not exactly matching numbers
 

Try:

=MATCH("*" & A1 & "*",C:C,0)

A1 is 10 digit
C:C are 12 digits

HTH


"Kelly" wrote:

I need to often match up columns on two spreadsheets. One of the xls has a
reference number of 12 digits...the other has 10 digit. The 10 digits are
the same as the 12 except they are missing the first and last number.

Is there a way to do a vlookup using a "wild" card type feature?

vlookup with not exactly matching numbers
 

and ...

=VLOOKUP("*" & A1 & "*",C:C,1,0)

also works.

"Kelly" wrote:

I need to often match up columns on two spreadsheets. One of the xls has a
reference number of 12 digits...the other has 10 digit. The 10 digits are
the same as the 12 except they are missing the first and last number.

Is there a way to do a vlookup using a "wild" card type feature?

vlookup with not exactly matching numbers
 

How should the numbers be formatted? The 12 digits are formatted as
"000000000000" because some of them begin with 0. Some of the 10 digits
begin with zero as well.

"Toppers" wrote:

and ...

=VLOOKUP("*" & A1 & "*",C:C,1,0)

also works.

"Kelly" wrote:

I need to often match up columns on two spreadsheets. One of the xls has a
reference number of 12 digits...the other has 10 digit. The 10 digits are
the same as the 12 except they are missing the first and last number.

Is there a way to do a vlookup using a "wild" card type feature?

vlookup with not exactly matching numbers
 

I had the numbers formated as text when I did a quick test but did have
leading zero(s).

"Kelly" wrote:

How should the numbers be formatted? The 12 digits are formatted as
"000000000000" because some of them begin with 0. Some of the 10 digits
begin with zero as well.

"Toppers" wrote:

and ...

=VLOOKUP("*" & A1 & "*",C:C,1,0)

also works.

"Kelly" wrote:

I need to often match up columns on two spreadsheets. One of the xls has a
reference number of 12 digits...the other has 10 digit. The 10 digits are
the same as the 12 except they are missing the first and last number.

Is there a way to do a vlookup using a "wild" card type feature?

vlookup with not exactly matching numbers
 

Thank you! You are a God Send!

"Toppers" wrote:

I had the numbers formated as text when I did a quick test but did have
leading zero(s).

"Kelly" wrote:

How should the numbers be formatted? The 12 digits are formatted as
"000000000000" because some of them begin with 0. Some of the 10 digits
begin with zero as well.

"Toppers" wrote:

and ...

=VLOOKUP("*" & A1 & "*",C:C,1,0)

also works.

"Kelly" wrote:

I need to often match up columns on two spreadsheets. One of the xls has a
reference number of 12 digits...the other has 10 digit. The 10 digits are
the same as the 12 except they are missing the first and last number.

Is there a way to do a vlookup using a "wild" card type feature?