#INVALID OPERATION is trying to break me

Rebekah Valdez-Pound

This formula is wrecking my day so I am waving the white flag and asking for help...

I am attempting to display the LOG 2 "Title Company" where the LOG 1 FC Start Date (020) .GT/ET. the LOG 2 "Effective Date" and LOG 1 FC Start Date (020) .LT/ET. the LOG 2 "End Date" and LOG 1 "Property State" = LOG 2 "Property State".

Formula: =INDEX(COLLECT({Title Co}, [FC Start Date (020)]@row >= {Eff Date}, [FC Start Date (020)]@row <= {End Date}, [Property State]@row, ={State}), 1)

LOG 1

LOG 2

Kelly Moore

Hey @Rebekah Valdez Pound

The syntax of the COLLECT function is COLLECT(data to collect, Range1, criteria1, range2, criteria2, etc).

=INDEX(COLLECT({Title Co}, {Eff Date}, <=[FC Start Date (020)]@row, {End Date}, >=[FC Start Date (020)]@row , {State},[Property State]@row), 1)

Does this return the value you expect?

Kelly Moore

Hey @Rebekah Valdez Pound

The syntax of the COLLECT function is COLLECT(data to collect, Range1, criteria1, range2, criteria2, etc).

=INDEX(COLLECT({Title Co}, {Eff Date}, <=[FC Start Date (020)]@row, {End Date}, >=[FC Start Date (020)]@row , {State},[Property State]@row), 1)

Does this return the value you expect?

Rebekah Valdez-Pound

@KDM It sure did! Thank you so much.

COLLECT is awesome when you use it correctly! Appreciate the assistance!