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.

 

[SunnyAlv]

Based on ur formula, try this

=VLOOKUP(VALUE(SUBSTITUTE(A2," Total","")),F:G,2,FALSE)

if u can using Textbefore

=VLOOKUP(VALUE(TEXTBEFORE(A2," ")),F:G,2,FALSE)

 

[RobP]

Another way :

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(MID(A2,1,LEN(A2)-6)),F:G,2,FALSE)

 

[Peter_SSs]

A slightly simpler version of post 3 suggestion would be:

=VLOOKUP(--LEFT(A2,LEN(A2)-6),F:G,2,0)

.. or you could use this in the top cell and not have to copy down the column

=VLOOKUP(--LEFT(A2:A16,LEN(A2:A16)-6),F:G,2,0)

 

[Cubist]

Another option:

=XLOOKUP(A2:A16,F2:F16&" Total",G2:G16)

 

[feni1388]

Thank you SunnyAlv, RobP, and Peter_SSs.
All your formula works great.

Cubist, I also tried yours, but it didn't work.

 

[Cubist]

Cubist, I also tried yours, but it didn't work.

Works fine for me.

Book2
	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	=XLOOKUP(A2:A16,F2:F16&" Total",G2:G16)
Dynamic array formulas.

 

[Peter_SSs]

@Cubist, I also tried yours, but it didn't work.

In what way did it not work? What result(s) did it return for that sample data?

If @Cubist's formula did not work, did the second formula I gave in post #4 work for you?

 

[feni1388]

In what way did it not work? What result(s) did it return for that sample data?

If @Cubist's formula did not work, did the second formula I gave in post #4 work for you?

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

 

[SunnyAlv]

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

#SPILL error means, the formula produces a 2D ARRAY and is blocked by the cell that contains the value below it