Index and Match based on partial match

[newbie188]

Hi. so I'm trying to populate a column in one sheet with info from another sheet. But the lookup value is slightly different in the target sheet. in the sheet I want to bring data into, the lookup value omits the leading 0. in the lookup sheet, it contains the leading 0 but omits the last six digits . can I use a % or * or something to indicate that as long as the lookup array has, say, six matching characters in order, it will bring in the data required?

 

[Georgiboy]

You mean something like this:

Book1
	A	B	C	D	E	F
1	12345678910	3				0
2						1
3						0123456
4						2
5						3
Sheet1

Cell Formulas
Range	Formula
B1	B1	=MATCH(0&LEFT(A1,6),F1:F5,0)

Or maybe:

Book1
	A	B	C	D	E	F
1	12345678910	3				0
2						1
3						012345699999
4						2
5						3
Sheet1

Cell Formulas
Range	Formula
B1	B1	=MATCH("*"&LEFT(A1,6)&"*",F1:F5,0)

 

[newbie188]

You mean something like this:

Book1
	A	B	C	D	E	F
1	12345678910	3				0
2						1
3						0123456
4						2
5						3
Sheet1

Cell Formulas
Range	Formula
B1	B1	=MATCH(0&LEFT(A1,6),F1:F5,0)

Or maybe:

Book1
	A	B	C	D	E	F
1	12345678910	3				0
2						1
3						012345699999
4						2
5						3
Sheet1

Cell Formulas
Range	Formula
B1	B1	=MATCH("*"&LEFT(A1,6)&"*",F1:F5,0)

perfect! thanks so much Georgiboy! I was able to incorporate this into an Index and match. just what i needed. thanks again