Assistance with index formula

JStehle

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

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

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.