Assign value in one row of cells based on next occurance of values in another row

I am compiling a census and need a formula that will look up the data found in Column A and return/categorize the results into one of four values (either E, ES, EC or FF) in Column B.

Column A either contains "E", "ES" or "C".

I need the formula in Column B to return the following:

If A16=blank, then B16=blank.

If A16 is not blank but does not contain "E", then B16=blank.

If A16=E, then look up in Column A beginning at A16 and see if "SP" occurs before the next occurrence of "E" in Column A, if yes, then keep looking down in Column A to see if "C" also occurs before the next "E", if yes, then B16 = FF.

If A16=E, then look up in Column A beginning at A16 and see if "SP" occurs before the next occurrence of "E" in Column A, if yes, then keep looking down in Column A to see if "C" also occurs before the next "E", if no, then B16 = ES.

If A16=E, then look up in Column A beginning at A16 and see if "SP" occurs before the next occurrence of "E" in Column A, if no, then keep looking down in Column A to see if "C" also occurs before the next "E", if yes, then B16 = EC.

If A16=E, then look up in Column A beginning at A16 and see if "SP" occurs before the next occurrence of "E" in Column A, if no, then keep looking down in Column A to see if "C" also occurs before the next "E", if yes, then B16 = E.

Attached is sample census file.

My prayers to God in Jesus' name for the solution to this challenge of mine and a big THANK YOU in advance to anyone who has the wisdom they can impart to me here.

Thank you, JBeaucaire; however, this does not return what I need. I followed your instructions and every result in Column B is either "E" or blank. But I need the results to either be E, ES, EC or FF, but none of the results are coming back ES, EC or FF - they are all coming back E.

So when looking at the first 5 rows of data in Column A, we have E, then SP, then C, then C, then E again. The formula needs to be able to come back with a result in B16 as FF; however, the solution you provided results in E, which is not correct for that individual employee (E) who actually has coverage for themselves, their Spouse (SP) and two children (C).

The result in Column B should only come back "E" if there are no SP or C occurrences after the E or before the next E.

Note: the formula would also need to be able to calculate correctly for the very last E listed on the census which will have no E after it.

Any other suggestions?

Almost perfect!! Thank you so much for your help. I had copied the formula in the "refers to" field incorrectly. Once I corrected it, I was able to receive correct results.

ONE MORE THING NEEDED THOUGH. I went to the last employee who on the same census I initially posted should be E which comes back correct, but if I have a census that has the last employee who has SP or C after them, and they are the last employee on the census, there will not be another E occurrence, and the formula that I have right now does not provide the correct enrollment code in Column B for the last row in this scenario I'm describing.

Is there some way to slightly modify the provided formula so the last member listed on the census will have the correct code result in Column B?

So if you take the same census as before but add a SP in cell A101 and add a C in cell A102 after the last E, you can see what issue I'm running into. B100 value should be FF but it's returning E because there's no other E occurrence in Column A after the E in row 100, but there won't be another E instance for the very last employee. How can we have the formula account for this?

Be Well -

See attached for updated sample census report. I've added SP and C after the last E and you'll see that "E" is the result coming back still in B100 but it should be FF.

Be Well -

JBeaucaire - I just thought of a real easy solution to my last issue. I'll just type "E" in a cell below my normal search criteria range in that one column and that will resolve the last employee on the census issue.

Thank you SO MUCH for sharing your wisdom with me. If you email me your mailing address, I'll put a $25 gift card in the mail to you...truly, your answer helps me IMMENSELY!.

Your last formula worked perfectly. Truly, I thank God for you!

Be Well...

Hi Jerry,

As it turns out, to complete the other formula I recently posted on this forum, I'm having to fill in all the blanks that are in one of my columns of information that I receive from the formula you provided to me. Instead of having your formula return a blank in Column B, are we able to modify the formula so the blank cells actually return the same value that is in Column B of the first row above it that contains an E in Column A? So for example, B68 and B69 would return FF since the value in A67 is E and the value in B67 is FF.