Jump to content

Searching Multiple Rows/Columns to Populate Column

Tom Wasylkiewicz

Recommended Posts

Hi, was hoping to get some help across comparing data from multiple columns with the goal of populating Column D. Here is an example:


Column A Column B Column C Column D

Oper1 9/1/1990


Oper3 3/1/2021

Aper1 9/1/1990

Aper2 3/1/2021


I want to find for "Oper" and "Aper" (always 4 digit string on left in column A) which event (1, 2, or 3) is relevant (dictated by most recent date). Hoping someone can help me identify a function that will search the rows for "Oper" and look at the most recent date in Columns B and C. Ideal output in column D would be "Relevant" for Oper 3 and Aper 2, and "Irrelevant" for the other 4 rows. Thanks guys.

Link to comment
Share on other sites

Output for Column D below. Basically, in row 1 (Oper1 row), the function in Column D should search for "Oper" (left 4 elements of string in Column A) in all other rows in Column A, and find which row has the latest date in columns B and C. If that iteration of "Oper" is the latest, Column D returns a string "Relevant". If another "Oper" row has in columns B or C a more recent date, then Column D in row 1 should return as "Irrelevant". 


Column A    Column B       Column C    Column D


Oper1           9/1/1990                              Irrelevant


Oper2                                                       Irrelevant


Oper3          3/1/2021                               Relevant


Aper1           9/1/1990                              Irrelevant


Aper2                                3/1/2021         Relevant  


Aper3                                                      Irrelevant

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...