Formula to return matching value

Gloria H.

Hi! I'm looking for help with a formula that will return the corresponding row value in [Dan Accrual] column when the value of the [Period] column is "Y". I was thinking this should be an index/match, but I can't quite puzzle it out. I'd be thrilled to have this result in the sheet summary, and not need to utilize an additional column.

Thanks!

Lucas Rayala

Hi @Gloria Perez@Gloria H. you should be able to reference this with this formula:

=INDEX(COLLECT([Dan Accrual]:[Dan Accrual], Period:Period, "Y"), 1)

The collect function will give you all instances where the Period is "Y", and the "1" at the end of the Index will provide the first instance. If it's an exact match and only 1 instance exists, then you will get that one.

Lucas Rayala

Hi @Gloria Perez@Gloria H. you should be able to reference this with this formula:

=INDEX(COLLECT([Dan Accrual]:[Dan Accrual], Period:Period, "Y"), 1)

The collect function will give you all instances where the Period is "Y", and the "1" at the end of the Index will provide the first instance. If it's an exact match and only 1 instance exists, then you will get that one.