Results 1 to 7 of 7

Thread: Formula to insert result based on search of data in multiple columns

  1. #1

    Formula to insert result based on search of data in multiple columns



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to figure out a solution for the following scenario: I have a spreadsheet with multiple rows and columns of data. I want to come up with a formula (or macro?) that will search the data that is entered in cells A2, A4, A6, A8, A10, A12 and insert the highest "level" results in cell A1 based on a hierarchy. Cells A2, A4, A6, A8, A10, A12 will either have "Level 1", "Level 2", "Exempt" or nothing entered. I want this formula (or macro?) to look in those cells and insert the highest "level" entry of my list, which is "Exempt", into cell A1. If there are no "Exempt" entries in the target cells, then it should insert the next highest level it finds ("Level 2"). If it doesn't find "Level 2" in the target cells, it needs to look for "Level 1" and insert that. If all target fields are blank, it will leave cell A1 blank. I have over 800 rows in the spreadsheet (each for a different vendor), so this formula or macro would have to be replicated for each row.

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Hello
    You don't say what version of Excel you have, but if its 2007 onwards then try this in Cell A1:

    =IFERROR(IF(FIND("Exempt", A2&A4&A6&A8&A9&A10&A12), "Exempt"),IFERROR(IF(FIND("Level 2", A2&A4&A6&A8&A10&A12), "Level 2"),IFERROR(IF(FIND("Level 1",A2&A4&A6&A8&A10&A12), "Level 1"),"")))

    HTH

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Reading your post again, I realised that the formula needs to be transportable to process other blocks of data, so this revision should be placed in Cell A1 to reference 6 alternate cells in Col A, starting at A2.
    If copied elsewhere, it will reference 6 alternate cells in the same column, starting at the cell below the one copied to.

    =IFERROR(IF(FIND("Exempt", CONCATENATE(A2,A4,A6,A8,A9,A10,A12)), "Exempt"),IFERROR(IF(FIND("Level 2", CONCATENATE(A2,A4,A6,A8,A10,A12)), "Level 2"),IFERROR(IF(FIND("Level 1",CONCATENATE(A2,A4,A6,A8,A10,A12)), "Level 1"),"")))

  4. #4
    Aside from the fact that you have added A( to the first test, what is the difference in those two formulas?

  5. #5
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    A mistake. I thought that the cell references wouldn't adjust correctly if the first formula was copied, but I was wrong. I suppose the bonus is a choice of formulae !
    Can't answer on the ( as Im not sure what you mean. Each test in the second formula has an extra pair of brackets to support the CONCATENATE function.
    Last edited by Bob Phillips; 2014-10-05 at 10:56 PM.

  6. #6
    Sorry, typo, I meant A9 not A(.

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by Bob Phillips View Post
    Sorry, typo, I meant A9 not A(.
    Sorry - The second formula (as below) should be treated as an alternative to the first and not a replacement, as both can be copied to return multiple results as required.

    =IFERROR(IF(FIND("Exempt", CONCATENATE(A2,A4,A6,A8,A10,A12)), "Exempt"),IFERROR(IF(FIND("Level 2", CONCATENATE(A2,A4,A6,A8,A10,A12)), "Level 2"),IFERROR(IF(FIND("Level 1",CONCATENATE(A2,A4,A6,A8,A10,A12)), "Level 1"),"")))

    Bob
    Thanks for your input.
    Last edited by Hercules1946; 2014-10-06 at 11:25 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •