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?

 

[Firasath]

You're welcome!

Thank you very much, it worked exactly as I wanted. Previously I was using IF, OR & Left combination and it was 5 lines string I need to use. You made it so easy. Thanks. But I'm also curious why didn't you suggested vlookup. Wont it work properly. Please explain.

 

[T. Valko]

Thank you very much, it worked exactly as I wanted. Previously I was using IF, OR & Left combination and it was 5 lines string I need to use. You made it so easy. Thanks. But I'm also curious why didn't you suggested vlookup. Wont it work properly. Please explain.

VLOOKUP wouldn't work because the various substrings could match multiple lookup values and VLOOKUP would stop at the first substring match which may not be the correct match.

 

[Firasath]

VLOOKUP wouldn't work because the various substrings could match multiple lookup values and VLOOKUP would stop at the first substring match which may not be the correct match.

Great. Thanks.

 

[T. Valko]

Great. Thanks.

You're welcome!