Assistance with index formula

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

Find more posts tagged with

Formulas

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…
INDEX/COLLECT??
Having trouble with an INDEX/COLLECT/CONTAINS formula. I have two sheets, my source and my destination; the source sheet has a column I want to collect into the destination. -Tool ID, is in both sheets and the target of the INDEX/MATCH, the source sheet might have this listed 6 or 7 times -Purpose of Device, there are…