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

katkth7533

New member
Joined
Jul 29, 2014
Messages
17
Reaction score
0
Points
0
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.
 
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
 
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"),"")))
 
Aside from the fact that you have added A( to the first test, what is the difference in those two formulas?
 
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 a moderator:
Sorry, typo, I meant A9 not A(.
 
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:
Back
Top