| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
advanced if?????
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 |
|
#2
|
|||
|
|||
|
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" wrote in message ... 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 |
|
#3
|
|||
|
|||
|
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" wrote in message ... 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" wrote in message ... 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 |
|
#4
|
|||
|
|||
|
Opps! You're right, my mistake :-)
KL "Biff" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
|
#5
|
|||
|
|||
|
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?????????????? -- taran ------------------------------------------------------------------------ taran's Profile: http://www.excelforum.com/member.php...o&userid=25724 View this thread: http://www.excelforum.com/showthread...hreadid=391338 |
|
#6
|
|||
|
|||
|
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 -- taran ------------------------------------------------------------------------ taran's Profile: http://www.excelforum.com/member.php...o&userid=25724 View this thread: http://www.excelforum.com/showthread...hreadid=391338 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| advanced filter criteria "begins with" and "does not begin with" | Excel Worksheet Functions | |||
| Using advanced filter to search for criteria in a list | Excel Discussion (Misc queries) | |||
| advanced filter - can't match a long text cell | Excel Discussion (Misc queries) | |||
| Advanced Find Command?? | Excel Discussion (Misc queries) | |||
| Can Excel advanced filter be used in "and NOT" mode | Excel Worksheet Functions | |||
Hybrid Mode
