Share via

How do I use the "=IF(ISNUMBER(SEARCH" Function in Excel to distinguish "1" from other numbers beginning with "1"?

Anonymous
2023-02-23T17:13:17+00:00

I am using Microsoft Excel Version 2008 (Build 13127.21216 Click-to-Run) (*.xlsx).  I need to have Cell K21 display the contents of a certain cell from a column of cells from Column AN containing text based on the number (containing numbers ranging from 1 – 23 with “6” divided into “6a” and “6b”) entered into Cell J21 by means of a drop-down list.

Here is a segment of the formula that I am using in Cell K21:

=IF(ISNUMBER(SEARCH("[List]",J21)),AN10,IF(ISNUMBER(SEARCH("1",J21)),AN11,IF(ISNUMBER(SEARCH("2",J21)),AN12,

In this case, the data in Cell J21 will signify a “Region” comprised of counties.  Therefore “1” in Cell J21 indicates Region number “1”.  The corresponding lists of counties for each of the 24 numerical Regions are found Cells 10 – 34 of Column AN.  The counties for Region “1” are listed in Cell AN11.

The problem seems to be that if the number in the search begins with the digit “1” (e.g., “12”) the formula only identifies the lowest numbered cell in the column (Column “AN”) in which the 1^st^ digit is “1” – that being Cell AN11.

In this case, when Cell J21 contains “12”, the data from Cell “AN23” (a list of counties separated by commas) should be inserted into Cell K21.  Instead, the data from Cell “AN11” corresponding to counties in Region “1” (rather than Region “12”) is inserted into Cell K21.

Everything works fine when the data in Cell J21 contains 1, 2, 3, 4, 5, 6a, 6b, 7, 8, and 9.  At this point however all numbers from 10 – 19 that appear in Cell J21 are treated as though the data is just “1”.  Likewise, when the numbers from 20 – 23 appear in Cell J21, the formula interprets them as just “2” and only inserts the list of counties from Region 2.

I have tried replacing the numerals with words (e.g., “One” for “1”) but the same thing happens.  I have also tried inserting the “Region” before the number with the same result.  I have change the formatting of the cells from “Number” (with “0” decimal spaces) to “General” to “Text” – all without effect.

What do I need to do to get the results that I need?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2023-02-23T18:24:48+00:00

    Glad to hear that the updated formula works for you!

    Regarding the "#N/A" error, hmmm could be because the INDEX and MATCH functions in the first part of the formula cannot find a match for "[List]" in the list of regions. Kindly try to wrap the formula in an IFERROR function, which returns a specified value (such as "[List]") if the formula results in an error.

    =IFERROR(IF(ISNUMBER(SEARCH("[List]",J21)),INDEX(AN10:AN34,MATCH(J21,{"1","2","3","4","5","6a","6b","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23"},0)),INDEX(AN11:AN34,MATCH(TRUE,EXACT(J21&"",{"1","2","3","4","5","6a","6b","7","8","9","10","11","12", "13","14","15","16","17","18","19","20","21","22","23"}),0))), "[List]")

    IFERROR basically checks if the formula results in an error, and if it does, it returns "[List]". If the formula does not result in an error, it returns the value from the formula.

    3 people found this answer helpful.
    0 comments

Answer accepted by question author

  1. Anonymous
    2023-02-23T17:58:56+00:00

    Hello, I am Leonielhou, an Independent Advisor and a user like you, I am happy to help clarify any questions you may have.

    Kindly try this one

    =IF(ISNUMBER(SEARCH("[List]",J21)),INDEX(AN10:AN34,MATCH(J21,{"1","2","3","4","5","6a","6b","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23"},0)),INDEX(AN11:AN34,MATCH(TRUE,EXACT(J21&"",{"1","2","3","4","5","6a","6b","7","8","9","10","11","12","13", "14","15","16","17","18","19","20","21","22","23"}),0)))

    So basically, it is to use the EXACT function instead of the SEARCH function. Its function compares two strings and returns TRUE if they are exactly the same, and FALSE otherwise. in the INDEX function for the AN11:AN34 range, we start at AN11 instead of AN10, since AN10 is already covered in the first part of the formula. Also, the list of regions in the EXACT function is enclosed in curly braces, which indicate an array constant. This allows us to compare J21 to multiple values at once, without having to repeat the MATCH function for each value.

    3 people found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-02-23T19:56:49+00:00

    OK Leon, you're COOL!

    Wow, I have struggled with this for some time. It sounds like your approach could solve the “too many arguments for this function” error that I've run into.

    For example, I've got a drop-down list of 159 counties (plus "[List]" as a sort of place holder) in Cell H5. Each of these 159 counties fall into 1 of 24 Regions that are listed by number in Cells AM10 - AM34. (There names separated by commas are in Cells AN10 - AN34.)

    Now using this formula: "=IF(ISNUMBER(SEARCH("[List]",H5)),AM8,IF(ISNUMBER(SEARCH("Appling",H5)),AM33, . . . " for each of the 159 counties that I select from in order to populate Cell H5 results in a “too many arguments for this function” error.

    For this reason I had to break up the 1 formula into 5 formulas in 5 different cells. Whatever number pops up in one of those 5 cells (in the case of Appling County it would be Region "22" from Cell AM33) I then manually select it from the drop-down menu in Cell J21.

    At that point, your function in the adjoining cell (K21) goes to work giving me the list of counties for Region 22 from Cell AN33 - voila!

    If I can figure out your formula (and that's a pretty big "if" my new friend) it seems that I could do away with the 5 different formulas for 5 different cells and a manual entry into Cell J21.

    I'm so grateful to you already, Leon. If I can't reverse engineer your formula, can you recommend a resource for learning more about this sort of thing? (Frankly, I'm technologically Amish . . . but am working to convert!) What do I even call these functions so I can look up material to teach me about it? (I've got a really nice set of encyclopedias at home! . . . that's a joke by the way, Leon.)

    Again, I can't thank you enough for your help. This is the first time I've availed myself of the Microsoft Community and I am overcome by the generosity you've shown with your time. Thank you!

    0 comments
  2. Anonymous
    2023-02-23T18:19:23+00:00

    Wow, that's GREAT, Leon! Thank you so much! Now, when J21 contains "[List]" K21 shows "#N/A" instead of "[List]". Certainly, I can live with that because the information that I really need in K21 shows up, now. If you know how to fit that however, it would be PERFECT!

    Seriously, Leon I can't thank you enough!

    0 comments