INDEX and MATCH in defined range from another INDEX and MATCH

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks for the response, uploaded sample sheet.
Sheet form is the target form I need to do and Data is the source of data (self explanatory, hopefully)

What I tried is to define start and end row for each ticket and MATCH within that range. Needless to say I wasn't very successful.

Lots of stuff, but no explanation.

What do you expect to see?
Where do you expect to see it?
Why?

Mmm. How should the ticket numbers in column A of Form get there? By formulae (criteria???) or by manual entry, or what??

I think it's clear now. Roughly how many rows of data do you have? Might be do-able by formula to get the uniques.

Mostly formulae are variants of this:
=IFERROR(INDEX(Data!J$2:J$200,MATCH(1,INDEX((Data!$B$2:$B$200=$A4)*(Data!$I$2:$I$200=$A$19),0),0)),"")

see sheet for details. Different shading = different formulae.

2000 might be a bit too many for a formula to handle... A lot of resource would be used.

Wow, I was expecting a hint or advice, not a complete solution with color coding. Thanks a bunch! Where's the donate button? :D

It's a one time calculation, when it's done we can repaste the formulas as values for better performance, or set manual recalc.

I'll try to understand what's going on over the weekend..

I deleted a few redundant rows, moved the search terms to another sheet, for clarity, and added a formula in column A to return uniques:
=IFERROR(INDEX(Data!$B$2:$B$200,MATCH(0,INDEX(COUNTIF($A$1:$A1,Data!$B$2:$B$200),0),0)),"")

Adjust the ranges to suit.

I copied all formulae down to the end of your data. I reshaded one column, as it should have had a different colour (different formula to column to its left).

There's no donate button. I do this 'cos I'm sick in the head.

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

BtW, no need to paste values, etc. It's not a volatile formula. It may take some time to grind through 2000 rows. But having done so, it won't need to do so again, unless/until you change the raw data TICs.

Thanks again! Repo added.
I found a slight issue - for example with TIC6443488, two Yes should be marked as per Data!I9 and Data!I7, but only one appears.

About to settle down to a pile of seafood. I'll be back in a few hours.

Are you saying that the same TIC can answer that question more than once? Do any of theblank fieldsprovide any differentiation between the answers?

Never mind. Figured it out. Back at PC a bit later on...

Completely revised formula in that section. Check it over & let me know if there are still any problems...

You're welcome!