Can you Index Match different sheets but have it check multiple rows also?

Riley S

I am trying to Index Match 2 different sheets and it works when I only have one row that matches. I am trying to match the newest row in a series entry.

Context: I have different quote numbers for different submissions on the master sheet form but I have another form on the SKU sheet that we use the same Quote number to input more data that goes with the quote number. I have it set up so that when a user fills out the sku form they check a box saying that they have no more information to input that relates to this quote number. This should trigger the master sheet to display a check box as well.

The issue I am running into is that there are multiple rows on the SKU sheet that contain the quote number on the master sheet so when I Index Match them it only checks the first submission under that quote number on the SKU sheet so it never triggers the Master sheet. I am wanting it to automatically check every row in the SKU sheet with the Quote number so it will automatically trigger.

The current formula I have is =INDEX({SKU Data Sheet Range 8}, MATCH([Quote Number]@row, {SKU Data Sheet Range 1}, 0))

George Lie

Hi Riley,

Try using =index(collect( formula to pull more than 1 criteria, since index + match only allows you to pull certain data from one criteria, if you have any unique number in your column to separate the difference between one SKU and the other, index + collect should help the issue.

=index(collect({SKU Data Sheet Range 8}, {Criterion Range1}, Criterion@row, {Criterion Range2}, Criterion@row),1)

Riley S

Thank you. I will try that. I have another question. In this formula can I put a criterion as a check box? That is currently the criterion that want to use.

Also the unique numbers are only kind of unique. There could be multiple of the same quote number that has multiple sku numbers on it.

I have tried this formula and all I get is an index error:

=INDEX(COLLECT({SKU Data Sheet Range 8}, {Quote Number}, [Quote Number]@row), 1)

No need for the help anymore. I was able to solve the issues.