Index Match With Unique Identifier

You can use the formula bellow.

Formula:

Please Login or Register  to view this content.

Thank you DJunqueira.

Have never used Xlookup or Pivot previously.

Will need to read up on them.

- Mik

XLOOKUP is a very simple function, look for this value in this column (or row) and return the value of the other column.

Hi DJunqueira,

Unfortunately this does not completely work.

Problem is relevant to an excerpt from my text above ..
"Some projects will have the same dates as others -- so without the ProjectDate identifier driving the formulas --- sheet will be sometimes be inaccurate."

If you go to sheet Table and change the date in c17 to from May 15 to May 17, xlookup will then return from row 2 on sheet Table - being "Event1 2024", as opposed to Row 4 being the start of Event3 2024 [denoted by Filter on Pivot].

Isolating pivot return cells by Project filter is critical. ...Which is why I was attempting the "helper" column K on Table.

Did I explain this clearly enough ..?

Thank you sincerely for having a close look at this.

- Mik

Hi DJunqueira,

Just wanted to let you know that you pointed me in the right direction.

Used "CityDate" helper field in the pivot, which *does* isolate by "Project" filter.

Then rearranged the XLOOKUP formulas a bit, and voila ..!

First time using XLOOKUP, didn't know it could lookup from table>right as opposed to vlookup, but took a chance and it worked.
Can see using it going forward.

Please see attached, and check w/Project in the filter.

Thanx again, appreciate the assist.

- Mik

ComplexIndexMatchUniqueIDhelper.xlsx

Tks for the feedback, glad to have helped.