Hello everyone - I have a complex lookup formula problem that I hope someone can help me with.
I’m working with a complex excel workbook that I’m trying to build out to include more visibility, and I’m stuck with “writer’s block” on an issue because I’m not an advanced user of combo formulae in Excel (which I think would solve the problem).
Here’s what I’m trying to do:
I want to insert a column that will indicate a value that is looked up in another cell using a formula that I can insert into multiple rows.
Here’s the problem:
At the top of the workbook, in the first 3 rows are fixed data elements/titles that follow the following pattern:
Row 1: space
Row 2: space
Row 3: date in the format of Month/Year (this is currently only listed above DE 2 below, but I can adjust to include an invisible/white date above each DE for simplicity)
Row 4:
Column 1: Data element 1 (DE1)
Column 2: DE 2
Column 3: DE 3
Column 4: DE 4
The pattern described above is repeated across 24 months
Now, the above pattern starts about 10 columns into the workbook as the first 9 columns contain a list of items we are tracking. One of these columns contains a value for the item that is plotted in one of the 24 months (in the columns to the right) in the SAME row. Here is an example of the pattern:
Column 1: Task #
Column 2-4: other identifying task information
Column 5: Description of task
Column 6: Amount
Column 7: The column I want to insert: date that the amount is plotted in to the right
For corresponding row items under column 7 (which I can move before column 6 if need be) that have a value, I want to look up values in column 6 in THAT row all the way to the right and when the value is found, it should display the date in the fixed row# 3 (and with a simple modification, will be in the same column that the value is found in).
I’m thinking because the date row (row 3) is fixed, and for each given item I’m doing a look up on, the row is known, there has to be a way to do a combo lookup formula that will find the value and display the date.
There are a couple other variables here that have prevented me from using a simple search for value or v/hlookup:
-If a given amount is not present under one of the dates, there are $0 (which need to remain there)
-The value may show up twice in the same row, sometimes under a different date pattern, and I need the date shown to be the FIRST time the value shows up for
-Given the above, there are 3 values in a given row after the display column I’m trying to insert: 0s, blanks, and the value that’s being searched for (sometimes listed twice)
-Because values may be the same for other items, I can’t do a simple search in the entire workbook – it has to be specific to the relevant row item
-Values aren’t necessarily always ascending
While I’d love just a solution to the display column above, the optimal solution would be a display/lookup formula that colors the cell green if the value is listed twice (which indicates the item is “finished”) and yellow if it’s listed only once (which indicates it’s “open” or wip).
Any help would be greatly appreciated!!
Thanks,
Mike
