Vlookup partial number

[iljott]

I'm a bit of an Excel noob and urgently need some help. This will probably be simple for some of you but I can't seem to figure it out myself.

I basically have an Excel sheet where Column A contains a dialing prefix and Column B contains a cost. I would like to enter a full number dialed and get the corresponding cost from Column B that has the most number of left-most digits from A. Below is a simple example:

A B
1 44 0.50
2 447 0.55
3 4470 0.60
4 4487 0.50
5 4489 0.75
6 44121276 1.00

Example 1: 4489123456 = 0.75 because the first 4 digits correspond to Row 5
Example 2: 4412345678 = 0.50 because only the first 2 digits correspond to Row 1
Example 3: 4412127612 = 1.00 because the first 7 digits correspond to Row 6

I was thinking of using VLookup, but I'm not sure how to go about this as I've only previously used VLookup with the same amount of digits to compare with.

Can anyone please help?

 

[T. Valko]

I'm a bit of an Excel noob and urgently need some help. This will probably be simple for some of you but I can't seem to figure it out myself.

I basically have an Excel sheet where Column A contains a dialing prefix and Column B contains a cost. I would like to enter a full number dialed and get the corresponding cost from Column B that has the most number of left-most digits from A. Below is a simple example:

A B
1 44 0.50
2 447 0.55
3 4470 0.60
4 4487 0.50
5 4489 0.75
6 44121276 1.00

Example 1: 4489123456 = 0.75 because the first 4 digits correspond to Row 5
Example 2: 4412345678 = 0.50 because only the first 2 digits correspond to Row 1
Example 3: 4412127612 = 1.00 because the first 7 digits correspond to Row 6

I was thinking of using VLookup, but I'm not sure how to go about this as I've only previously used VLookup with the same amount of digits to compare with.

Can anyone please help?

Maybe this...

Book1
	A	B	C	D	E
2	44	0.5	_	4489123456	0.75
3	447	0.55	_	4412345678	0.5
4	4470	0.6	_	4412127612	1
5	4487	0.5	_	_	_
6	4489	0.75	_	_	_
7	44121276	1	_	_	_
Sheet1

Formula entered in E2 and copied down:

=LOOKUP(1E100,SEARCH(A$2:A$7,D2),B$2:B$7)

 

[iljott]

Maybe this...

Book1
*	A	B	C	D	E
2	44	0.5	_	4489123456	0.75
3	447	0.55	_	4412345678	0.5
4	4470	0.6	_	4412127612	1
5	4487	0.5	_	_	_
6	4489	0.75	_	_	_
7	44121276	1	_	_	_
Sheet1

Formula entered in E2 and copied down:

=LOOKUP(1E100,SEARCH(A$2:A$7,D2),B$2:B$7)

Hi T. Valko,

Thanks for your great response! This almost works perfectly! The only thing is that the lookup looks everywhere within the number I give it - for example, if I enter 123447, I would get the value 0.55 (because of the 447), but I actually only want to use it if the numbers are at the beginning of the cell (I have a much longer list than 6 rows ).

 

[T. Valko]

Hi T. Valko,

Thanks for your great response! This almost works perfectly! The only thing is that the lookup looks everywhere within the number I give it - for example, if I enter 123447, I would get the value 0.55 (because of the 447), but I actually only want to use it if the numbers are at the beginning of the cell (I have a much longer list than 6 rows ).

Maybe this...

=LOOKUP(1E100,SEARCH(A$2:A$7,LEFT(D2,LEN(A$2:A$7))),B$2:B$7)

 

[iljott]

Maybe this...

=LOOKUP(1E100,SEARCH(A$2:A$7,LEFT(D2,LEN(A$2:A$7))),B$2:B$7)

Great!!! This did the trick! I can't thank you enough T. Valko!!

 

[T. Valko]

Great!!! This did the trick! I can't thank you enough T. Valko!!

You're welcome. Thanks for the feedback!

 

[Firasath]

You're welcome. Thanks for the feedback!

Hi Valko,

In the mentioned formula, I couldn't understand what IE100 is stands for. Kindly elaborate the same.

 

[T. Valko]

Hi Valko,

In the mentioned formula, I couldn't understand what IE100 is stands for. Kindly elaborate the same.

SEARCH will return either an error or a number from 1 to 32,767.

In order for LOOKUP to work correctly we need a lookup value that is >=32,767.

So, I use 1E100 which is scientific notation (shorthand) for the very large number 1 followed by 100 zeros. It's easy to remember 1E100.

 

[Firasath]

SEARCH will return either an error or a number from 1 to 32,767.

In order for LOOKUP to work correctly we need a lookup value that is >=32,767.

So, I use 1E100 which is scientific notation (shorthand) for the very large number 1 followed by 100 zeros. It's easy to remember 1E100.

Thanks for your quick response.

 

[T. Valko]

Thanks for your quick response.

You're welcome!