Search a mobile phone prefix in a table array

C

Chulop

New Member
Joined
Feb 21, 2014
Messages
4
I have 2 tables:
A) a list of 3,000+ names with corresponding mobile numbers.
B) a list of mobile phone prefixes (4 digits) belonging to 3 mobile phone carriers.
Question: I want to know what mobile phone carrier each of the 3,000 mobile numbers belongs to by matching the first 4 digits of the telephone numbers in table A with table B. What is the correct formula to use?
I tried: =IFERROR(VLOOKUP(E13,LEFT(E13,4),$I$3:$I$16,1),"Smart") but all #NAs are converted to Smart which is the wrong mobile phone carrier
=IF(VLOOKUP(E9,LEFT(E9,4),$I$3:$I$17,1),"Smart") returns a value of #NA even if the correct mobile carrier is Smart
Will appreciate receiving your reply in marichulpz@yahoo.com.ph
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
[TABLE="width: 781"]
<colgroup><col span="2"><col><col span="9"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name1[/TD]
[TD="align: right"]1234666666[/TD]
[TD]smart[/TD]
[TD]<<<<<<[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1234[/TD]
[TD]smart[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name2[/TD]
[TD="align: right"]2345777777[/TD]
[TD]clever[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2345[/TD]
[TD]clever[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name3[/TD]
[TD="align: right"]3456444444[/TD]
[TD]brainy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3456[/TD]
[TD]brainy[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name4[/TD]
[TD="align: right"]1234666667[/TD]
[TD]smart[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name5[/TD]
[TD="align: right"]2345777778[/TD]
[TD]clever[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name6[/TD]
[TD="align: right"]3456444445[/TD]
[TD]brainy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name7[/TD]
[TD="align: right"]1234666668[/TD]
[TD]smart[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name8[/TD]
[TD="align: right"]2345777779[/TD]
[TD]clever[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name9[/TD]
[TD="align: right"]3456444446[/TD]
[TD]brainy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name10[/TD]
[TD="align: right"]3456444447[/TD]
[TD]brainy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]formula in cell marked <<<<<[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]=VLOOKUP(VALUE(LEFT(C12,4)),mytable,2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
oldbrewer said:
[TABLE="width: 781"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name1[/TD]
[TD="align: right"]1234666666[/TD]
[TD]smart[/TD]
[TD]<<<<<<[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1234[/TD]
[TD]smart[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name2[/TD]
[TD="align: right"]2345777777[/TD]
[TD]clever[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]2345[/TD]
[TD]clever[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name3[/TD]
[TD="align: right"]3456444444[/TD]
[TD]brainy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]3456[/TD]
[TD]brainy[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name4[/TD]
[TD="align: right"]1234666667[/TD]
[TD]smart[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name5[/TD]
[TD="align: right"]2345777778[/TD]
[TD]clever[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name6[/TD]
[TD="align: right"]3456444445[/TD]
[TD]brainy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name7[/TD]
[TD="align: right"]1234666668[/TD]
[TD]smart[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name8[/TD]
[TD="align: right"]2345777779[/TD]
[TD]clever[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name9[/TD]
[TD="align: right"]3456444446[/TD]
[TD]brainy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]name10[/TD]
[TD="align: right"]3456444447[/TD]
[TD]brainy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]formula in cell marked <<<<<[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]=VLOOKUP(VALUE(LEFT(C12,4)),mytable,2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Thanks for your reply. I get an #NA even if the 1st 4 digits is on my table named "Prefix".

The formula I used was: =VLOOKUP(E8,(LEFT(E8,4)),Prefix,2)

The table prefix has 2 mobile phone carriers, with each having at least 10 different prefixes.

The prefixes are sorted in ascending order
 
Upvote 0
re why =IFERROR(VLOOKUP(E13,LEFT(E13,4),$I$3:$I$16,1),"Smart") doesnt work (all #NAs are converted to Smart which is the wrong mobile phone carrier) that's exactly what the ISERROR function does

the idea is that if the initial vlookup VLOOKUP(E13,LEFT(E13,4),$I$3:$I$16,1) returns an #N/A that you put some more user-friendly text eg "** unknown vendor **"
you have specified thatin event of an error put "Smart" as the result
 
Upvote 0
Chulop said:
Thanks for your reply. I get an #NA even if the 1st 4 digits is on my table named "Prefix".

The formula I used was: =VLOOKUP(E8,(LEFT(E8,4)),Prefix,2)

The table prefix has 2 mobile phone carriers, with each having at least 10 different prefixes.

The prefixes are sorted in ascending order
Click to expand...


You missed a very important part of oldbrewers formula
oldbrewer's formula =VLOOKUP(VALUE(LEFT(C12,4)),mytable,2) note the value function around the LEFT function.
this works , I didnt know how to sdo this either until I read oldbrewers post (thanks oldbrewer)

and the prefix ttable doesnt need to be in order
 
Upvote 0
I discovered the error was in the custom format of the mobile number. Most numbers were entered with a 0 in the prefix so they were given a custom format of 0##########. The prefix table was also given a custom format of 0###. Still an error of #NA.

I tried the long way of extracting the prefix from the mobile number using =(LEFT, 4) then copy and paste it as a value in another column. Still get an error of #NA even if both columns have the same custom formatting.

However, if I enter the prefix manually, eg 920 & remove the custom formatting from the prefix table, the correct value is returned, eg. 920 = Smart.
Manual copying will however be tedious for 3000+ mobile phone numbers.
 
Upvote 0
can you post some data . oldbrewers formula (combined here with your iserror) works for me

formula =IFERROR(VLOOKUP(VALUE(LEFT(B2,4)),Sheet6!$A$2:$Z$10,2,FALSE),"** UNKNOWN SUPPLIER **")

main sheet

[TABLE="width: 246"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 150pt; mso-width-source: userset; mso-width-alt: 7314" width=200><TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]name[/TD]
[TD="width: 64, bgcolor: transparent"]number[/TD]
[TD="width: 200, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]al[/TD]
[TD="bgcolor: transparent, align: right"]12345555[/TD]
[TD="bgcolor: transparent"]BigPhoneC[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]beth[/TD]
[TD="bgcolor: transparent, align: right"]24567890[/TD]
[TD="bgcolor: transparent"]AN Other Co[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]col[/TD]
[TD="bgcolor: transparent, align: right"]12346666[/TD]
[TD="bgcolor: transparent"]BigPhoneC[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]deb[/TD]
[TD="bgcolor: transparent, align: right"]88881111[/TD]
[TD="bgcolor: transparent"]Cheap Phones[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ed[/TD]
[TD="bgcolor: transparent, align: right"]24569999[/TD]
[TD="bgcolor: transparent"]AN Other Co[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]fran[/TD]
[TD="bgcolor: transparent, align: right"]24689898[/TD]
[TD="bgcolor: transparent"]Rich Phone Co[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]gail[/TD]
[TD="bgcolor: transparent, align: right"]24568765[/TD]
[TD="bgcolor: transparent"]AN Other Co[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]harri[/TD]
[TD="bgcolor: transparent, align: right"]88881111[/TD]
[TD="bgcolor: transparent"]Cheap Phones[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]inga[/TD]
[TD="bgcolor: transparent, align: right"]24687654[/TD]
[TD="bgcolor: transparent"]Rich Phone Co[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]jon[/TD]
[TD="bgcolor: transparent, align: right"]12344321[/TD]
[TD="bgcolor: transparent"]BigPhoneC[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]kate[/TD]
[TD="bgcolor: transparent, align: right"]24687777[/TD]
[TD="bgcolor: transparent"]Rich Phone Co[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]len[/TD]
[TD="bgcolor: transparent, align: right"]55557777[/TD]
[TD="bgcolor: transparent"] * UNKNOWN SUPPLIER*[/TD]
[/TR]
</TBODY>[/TABLE]







lookup sheet

[TABLE="width: 151"]
<TBODY>[TR]
[TD]code</SPAN>[/TD]
[TD]name</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1234</SPAN>[/TD]
[TD]BigPhoneC</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2468</SPAN>[/TD]
[TD]Rich Phone Co</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2456</SPAN>[/TD]
[TD]AN Other Co</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]8888</SPAN>[/TD]
[TD]Cheap Phones</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]
[TABLE="width: 151"]
<TBODY>[TR]
[TD]code</SPAN>[/TD]
[TD]name</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]1234</SPAN>[/TD]
[TD]BigPhoneC</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2468</SPAN>[/TD]
[TD]Rich Phone Co</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2456</SPAN>[/TD]
[TD]AN Other Co</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]8888</SPAN>[/TD]
[TD]Cheap Phones</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]
 
Upvote 0
[TABLE="width: 556"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Here's sample data, with custom formatting for mobile numbers (0##########) and prefixes (0###)

[TABLE="width: 556"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Name of Farmer[/TD]
[TD]Contact Number[/TD]
[TD][/TD]
[TD]Prefix[/TD]
[TD]Carrier[/TD]
[/TR]
[TR]
[TD]Charity[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]0994[/TD]
[TD] Globe [/TD]
[/TR]
[TR]
[TD]Joeven[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]0996[/TD]
[TD] Globe [/TD]
[/TR]
[TR]
[TD]Reynaldo[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]0997[/TD]
[TD] Globe [/TD]
[/TR]
[TR]
[TD]Danilo [/TD]
[TD]09301978108[/TD]
[TD][/TD]
[TD="align: right"]0813[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Percillano[/TD]
[TD]09214693234[/TD]
[TD][/TD]
[TD="align: right"]0907[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Rolando[/TD]
[TD]09107172000[/TD]
[TD][/TD]
[TD="align: right"]0908[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Romeo[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]0909[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Felipe Jr.[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]0910[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Julius[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]0912[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Mary Ann[/TD]
[TD]09213517604[/TD]
[TD][/TD]
[TD="align: right"]0918[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Rodolfo[/TD]
[TD]NA[/TD]
[TD][/TD]
[TD="align: right"]0919[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Leonardo[/TD]
[TD]09106712969[/TD]
[TD][/TD]
[TD="align: right"]0920[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Leonardo[/TD]
[TD]09105514110[/TD]
[TD][/TD]
[TD="align: right"]0921[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Richard[/TD]
[TD]09467133550[/TD]
[TD][/TD]
[TD="align: right"]0922[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Carmelita[/TD]
[TD]09074456537[/TD]
[TD][/TD]
[TD="align: right"]0923[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Marvin[/TD]
[TD]09355888868[/TD]
[TD][/TD]
[TD="align: right"]0925[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Mary Ann[/TD]
[TD]09267986797[/TD]
[TD][/TD]
[TD="align: right"]0928[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Amelito[/TD]
[TD]09057785322[/TD]
[TD][/TD]
[TD="align: right"]0929[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Ellardo[/TD]
[TD]09305862200[/TD]
[TD][/TD]
[TD="align: right"]0930[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Jeffrey[/TD]
[TD]09478011134[/TD]
[TD][/TD]
[TD="align: right"]0932[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Loreto [/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]0933[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Reynanti[/TD]
[TD]none[/TD]
[TD][/TD]
[TD="align: right"]0934[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Rodolfo[/TD]
[TD]09082178307[/TD]
[TD][/TD]
[TD="align: right"]0938[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Vivian[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]0939[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Jodie[/TD]
[TD]09465226297[/TD]
[TD][/TD]
[TD="align: right"]0942[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Alberto[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]0943[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Carlito Jr.[/TD]
[TD]09096216428[/TD]
[TD][/TD]
[TD="align: right"]0946[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Edgar[/TD]
[TD]09301375983[/TD]
[TD][/TD]
[TD="align: right"]0947[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Edwin[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]0948[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Elmer[/TD]
[TD]09464127488[/TD]
[TD][/TD]
[TD="align: right"]0949[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Myrna[/TD]
[TD]09469103563[/TD]
[TD][/TD]
[TD="align: right"]0989[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Ramil[/TD]
[TD]none[/TD]
[TD][/TD]
[TD="align: right"]0998[/TD]
[TD] Smart [/TD]
[/TR]
[TR]
[TD]Harold[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]0999[/TD]
[TD] Smart [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
you ? redundant ? absolutely not !! I had had that same problem with the vlookup before I saw your formula nesting left inside value ...

no longer pulling my hair out thanks to you
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Clean up List of Phone Numbers
2
Replies
11
Views
1K
alansidman
A
S
  • Question Question
Vlookuping matching values with hidden rounding errors
Replies
5
Views
479
awoohaw
awoohaw
C
  • Solved
If VLOOKUP is @NA check Table 2
Replies
9
Views
760
Joe4
Joe4
B
  • Question Question
Separating phone numbers from text fields, keeping all the data, then moving it around a bit. And breathe.
2
Replies
11
Views
2K
KP117
K
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
172
SBExcel123
S

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top