Assistance with index formula

JStehle · 06/13/19

I'm in need of assistance on an Index formula. The situation is I have a master sheet with a unique number in the Primary Column. Another column has a flag. In my second sheet, I would like returned a list of the unique numbers from the master sheet where the flag=true. I have tried so many versions of this formula that I don't know what is up any more. Currently I have:

=IFERROR(INDEX(@{EDI Backlog Details tab Range 1}, {EDI Backlog Details tab Range 16}@ROW, =true, [Column13]:[Column13]@ROWS),"")

Can anyone assist with this formula?

Thank you,

Judy

L_123 · 06/13/19

How I normally handle this is with a collect.

=iferror(index(collect({EDI Backlog Details tab Range 1},{EDI Backlog Details tab Range 16},true),1),"")

will return the first matching value. You can get fancy and do a self reference and iferror to automate this. I don't know what column you have the formula on so I will call it column A. To automate this, put the above formula in row 1, and the below formula in row 2. Then you can drag down the row 2 formula

=iferror(index(collect({EDI Backlog Details tab Range 1},{EDI Backlog Details tab Range 16},true),count(A$1:A1),"")

Side note: I highly recommend you always name your ranges. If you come back in 6 months you likely won't remember what they are, and other people definitely won't know what they are, so it makes troubleshooting in the future a pain.

JStehle · 06/13/19

Thank you. The first one worked perfectly.

The second one is returning a circular reference error. I changed the reference from A$1:A1 to [Column13]5:[Column13]152. My first formula is on row 4, the second on row 5. The full formula now is:

=IFERROR(INDEX(COLLECT({EDI Backlog Details tab Range 1}, {EDI Backlog Details tab Range 16}, true), COUNT([Column13]5:[Column13]152), ""))

Good point about naming the ranges. Once I get this formula going I'm going to go back and rename them all.

Assistance with index formula

Comments

Help Article Resources

Categories