advanced if?????

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

thankx for that .... do you know whether the range lookup should be true or false??? sorry to bug you but this has confused me all day

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>

Hi i tried everything that Biff said to do, but when i enter the formula

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

the cell comes up blank. no error, or anythimg.

this formula is too advanced for me so i cant figure out where it might have gone wrong

any ideas??????????????

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>

Hi,

Assuming that the searched phone number and the ones in the table are either
numeric values or text at the same time, if you change your table to the
below a formula like =VLOOKUP(D1,A1:B10,2) should work:

0121000000 25
0121200000 26
0121240000 27
0121249000 27
0121249357 24
0130000000 24
0131000000 25
0131200000 26
0131246300 27
0131246452 24


Regards,
KL


"taran" <[email protected]> wrote in message
news:[email protected]...
>
> Hi,
>
> I have a spreadsheet which looks a bit like this..
>
> Phone number cost
>
> 0121 25
> 01212 26
> 012124 27
> 0121249 27
> 0121249357 24
> 013 24
> 0131 25
> 01312 26
> 01312463 27
> 0131246452 24
>
> im sorry but my example table is not very good. fyi the numbers after
> the space in each row should be the next column...
>
> I need to find a way to cost the phone numbers i enter
> eg. if i need to find out how much 0121249357 will cost it will tell
> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>
> is there a way i can get excel to look thru a string of say 9 digits,
> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
> then 6 till it finds a match???
>
> sorry if im not very clear but i dont really know excel or the
> terminology very well.
>
> would be very grateful if some1 could help.
>
> Thanks,
>
> Taran
>
>
> --
> taran
> ------------------------------------------------------------------------
> taran's Profile:
> http://www.excelforum.com/member.php...o&userid=25724
> View this thread: http://www.excelforum.com/showthread...hreadid=391338
>

Hi!

If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
the formula would return 24 since 0121249357 is less than 0121249367.

You could string together a bunch of VLOOKUPs or MATCHes that search for the
various length string until a match is found but the resulting formula would
be quite long.

The OP mentioned wanting to search for string starting with 9 characters and
then decrementing but the longest strings in the posted examples are 10
characters.

Here's one way that uses a helper column. I'm assuming the phone numbers are
formatted as text so as to enable the leading zero.

Phone numbers in the range A2:An and corresponding value in the range B2:Bn

D2 = lookup value = 0121249367

Enter this formula in D3 and copy down to D11:

=LEFT(D$2,LEN(D2)-1)

Enter this formula in E2 and copy down to E11:

=IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))

This is what the output will look like:

0121249367
012124936
01212493
0121249 27
012124
01212
0121
012
01
0


Hope that little table doesn't get all messed up when posted!

Then, if you wanted to clean that up a bit you could use conditional
formatting to hide all but the matched values and the lookup value.

Biff

"KL" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
>
> Assuming that the searched phone number and the ones in the table are
> either numeric values or text at the same time, if you change your table
> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>
> 0121000000 25
> 0121200000 26
> 0121240000 27
> 0121249000 27
> 0121249357 24
> 0130000000 24
> 0131000000 25
> 0131200000 26
> 0131246300 27
> 0131246452 24
>
>
> Regards,
> KL
>
>
> "taran" <[email protected]> wrote in
> message news:[email protected]...
>>
>> Hi,
>>
>> I have a spreadsheet which looks a bit like this..
>>
>> Phone number cost
>>
>> 0121 25
>> 01212 26
>> 012124 27
>> 0121249 27
>> 0121249357 24
>> 013 24
>> 0131 25
>> 01312 26
>> 01312463 27
>> 0131246452 24
>>
>> im sorry but my example table is not very good. fyi the numbers after
>> the space in each row should be the next column...
>>
>> I need to find a way to cost the phone numbers i enter
>> eg. if i need to find out how much 0121249357 will cost it will tell
>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>
>> is there a way i can get excel to look thru a string of say 9 digits,
>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>> then 6 till it finds a match???
>>
>> sorry if im not very clear but i dont really know excel or the
>> terminology very well.
>>
>> would be very grateful if some1 could help.
>>
>> Thanks,
>>
>> Taran
>>
>>
>> --
>> taran
>> ------------------------------------------------------------------------
>> taran's Profile:
>> http://www.excelforum.com/member.php...o&userid=25724
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=391338
>>
>
>

Opps! You're right, my mistake :-)

KL


"Biff" <[email protected]> wrote in message
news:%[email protected]...
> Hi!
>
> If the lookup value was 0121249367 using the TRUE argument in the VLOOKUP,
> the formula would return 24 since 0121249357 is less than 0121249367.
>
> You could string together a bunch of VLOOKUPs or MATCHes that search for
> the various length string until a match is found but the resulting formula
> would be quite long.
>
> The OP mentioned wanting to search for string starting with 9 characters
> and then decrementing but the longest strings in the posted examples are
> 10 characters.
>
> Here's one way that uses a helper column. I'm assuming the phone numbers
> are formatted as text so as to enable the leading zero.
>
> Phone numbers in the range A2:An and corresponding value in the range
> B2:Bn
>
> D2 = lookup value = 0121249367
>
> Enter this formula in D3 and copy down to D11:
>
> =LEFT(D$2,LEN(D2)-1)
>
> Enter this formula in E2 and copy down to E11:
>
> =IF(ISNA(MATCH(D2,A$2:A$11,0)),"",IF(COUNT(E$1:E1),"",VLOOKUP(D2,A$2:B$11,2,0)))
>
> This is what the output will look like:
>
> 0121249367
> 012124936
> 01212493
> 0121249 27
> 012124
> 01212
> 0121
> 012
> 01
> 0
>
>
> Hope that little table doesn't get all messed up when posted!
>
> Then, if you wanted to clean that up a bit you could use conditional
> formatting to hide all but the matched values and the lookup value.
>
> Biff
>
> "KL" <[email protected]> wrote in message
> news:%[email protected]...
>> Hi,
>>
>> Assuming that the searched phone number and the ones in the table are
>> either numeric values or text at the same time, if you change your table
>> to the below a formula like =VLOOKUP(D1,A1:B10,2) should work:
>>
>> 0121000000 25
>> 0121200000 26
>> 0121240000 27
>> 0121249000 27
>> 0121249357 24
>> 0130000000 24
>> 0131000000 25
>> 0131200000 26
>> 0131246300 27
>> 0131246452 24
>>
>>
>> Regards,
>> KL
>>
>>
>> "taran" <[email protected]> wrote in
>> message news:[email protected]...
>>>
>>> Hi,
>>>
>>> I have a spreadsheet which looks a bit like this..
>>>
>>> Phone number cost
>>>
>>> 0121 25
>>> 01212 26
>>> 012124 27
>>> 0121249 27
>>> 0121249357 24
>>> 013 24
>>> 0131 25
>>> 01312 26
>>> 01312463 27
>>> 0131246452 24
>>>
>>> im sorry but my example table is not very good. fyi the numbers after
>>> the space in each row should be the next column...
>>>
>>> I need to find a way to cost the phone numbers i enter
>>> eg. if i need to find out how much 0121249357 will cost it will tell
>>> me 24 but if i need to find 0121249367 i need it to find 27 (0121249)
>>>
>>> is there a way i can get excel to look thru a string of say 9 digits,
>>> but if it doesnt find a match then look thru the 1st 8 digits, then 7,
>>> then 6 till it finds a match???
>>>
>>> sorry if im not very clear but i dont really know excel or the
>>> terminology very well.
>>>
>>> would be very grateful if some1 could help.
>>>
>>> Thanks,
>>>
>>> Taran
>>>
>>>
>>> --
>>> taran
>>> ------------------------------------------------------------------------
>>> taran's Profile:
>>> http://www.excelforum.com/member.php...o&userid=25724
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=391338
>>>
>>
>>
>
>