Index Collect Formula (i think) question

Ryan Doyle

I am trying to return a value from another sheet based on 2 pieces of criteria. I am looking to return the "Date Requested" data based on the "Operator" and "Request Type" criteria. This is the formula I am using. I am getting an INCORRECT ARGUMENT error however. Appreciate any guidance

=INDEX(COLLECT({Operator}, Operator@row, {Request Type}, [Request Type]@row, {Date Requested}, 0))

Ryan Doyle

I've updated my formula to this, and now getting an INVALID VALUE error

=INDEX(COLLECT({Date Requested}, {Operator}, Operator@row, {Request Type}, [Request Type]@row), 1)

Kelly Moore

Hey @Ryan Doyle

Is the column type where your formula sits a DATE column? Your COLLECT data appears to be a date value.

Kelly

Ryan Doyle

Hi @Kelly Moore

Yes, the column type where my formula sits is a DATE column.

Kelly Moore

@Ryan Doyle

Is it possible for screenshots of your source and destination sheets? As a test you can run, first delete {Request Type}, [Request Type]@row from the formula. Do you still get an error? If not, put the Request type range-criteria back in the formula and take the Operator range-criteria out of the formula. This will tell you what criteria is a problem.

Kelly

Humashankar

Good analysis

Ryan Doyle

@Kelly Moore

I was able to solve my problem. Ultimately I had the formula correct. I had mismatching values between the source sheet and the destination sheet. I was so focused on the complexity of the formula that i overlooked the easy part.

thank you so much for helping!