Lookup partial number

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?

Originally Posted by tlafferty

try sorting your data then use TRUE as the final argument instead of false.

Hi tlafferty,

Thanks for your quick reply. My data is already sorted in ascending order (Column A - sorry it's a bit difficult to see it properly above). If I used TRUE I wouldn't get an answer since the number I want to match is longer than any of the numbers in the lookup range.

Originally Posted by Haseeb A

Hello iljott, Welcome to the forum!



If so, try this.

Say in D1, your lookup number,

=LOOKUP(40000,SEARCH($A$1:$A$6,D1),$B$1:$B$6)

Hi Haseeb,

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 ).

Originally Posted by Haseeb A

Try this one,

=IFERROR(LOOKUP(40000,SEARCH(LEFT(D1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(D1)))),$A$1:$A$6),$B$1:$B$6),"Nothing Found")

I keep getting the "The formula you typed contains an error" message

Must be something with the parentheses but I'm not sure where.

I did a little bit of work on the first suggestion and I managed to get it to work:

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

Thanks so much for all your help!