More dynamic vlookup formula that will allow to search a certain digits of number

[feni1388]

Hello everyone....

I want to know whether it's possible to replace below formula with something more dynamic or not.

=VLOOKUP(VALUE(LEFT(A2,7)),F:G,2,FALSE)

My table data is as the following.

The problem is that the invoice number will grow to 8 digits and maximum 10 digits before it will be reset and go back to 1 digit number.
So is it possible to change the above formula to something that will vlookup only the numbers (no matter how many digits there're)?

Book1
	A	B	C	D	E	F	G
1	Invoice No.	Result				Invoice No.	Result
2	1000013 Total	15				1000013	15
3	1000014 Total	100				1000014	100
4	1000015 Total	30				1000015	30
5	1000016 Total	35				1000016	35
6	1000017 Total	40				1000017	40
7	1000018 Total	150				1000018	150
8	1000019 Total	200				1000019	200
9	1000020 Total	225				1000020	225
10	1000021 Total	89				1000021	89
11	1000022 Total	90				1000022	90
12	1000023 Total	95				1000023	95
13	1000024 Total	135				1000024	135
14	1000025 Total	250				1000025	250
15	1000026 Total	275				1000026	275
16	1000027 Total	55				1000027	55
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=VLOOKUP(VALUE(LEFT(A2,7)),F:G,2,FALSE)

Thank you in advance.

 

[Peter_SSs]

Now I tried the second one, and it returned SPILL .... just like Cubist's formula.

Delete all the cells below the first formula. My second formula (& Cubists) provide all the results from a single formula. It should not be copied down the column and all the cells below the formula should be empty to start with.

 

[feni1388]

Delete all the cells below the first formula. My second formula (& Cubists) provide all the results from a single formula. It should not be copied down the column and all the cells below the formula should be empty to start with.

Ah... I see...
I deleted everything below the first formula and it worked... thank you

 

[Peter_SSs]

I deleted everything below the first formula and it worked... thank you

Cheers. Thanks for the confirmation. So the point was that you can get all the results with a single formula rather than 15 formulas, or maybe hundreds or thousands of formulas if your full data is larger.