Index Match Row Number Update

Kaykay

Hello,

I have an index match formula but when I drag it down, the row number doesn't get updated and just drags the same formula down. Is there a way to drag this down and have the formula update rows?

Formula:

=INDEX(COLLECT({OSS Test Range 1}, {OSS Test Range 4}, "New"), 1)

I'm referring to the number 1 at the end of the formula. I also tried to convert to column formula but it all sets to the same one with row 1

Jeff Reisman

@Kaykay The 1 at the end of your formula is not a row number. The 1 tells the formula to return the first item in the collection.

Example: =INDEX(COLLECT([Transaction Total]:[Transaction Total], [In Stock?]:[In Stock?], true), 1)

Collects the values in the Transaction Total column for rows where the check box in the In Stock? column is checked (true), and returns the first entry in that collection.

Can you share some more information about the data you're trying to collect and what you're trying to do with it?

Kaykay

Hi @Jeff Reisman,

Thank you so much for the clarification!

Makes sense, is there a way to update the formula when dragged down to return the 2nd,3rd,4th...etc value.

I'm trying to return a name of city based on the status "new" in a field. I want to return all values of cities in a new column in a different sheet if they are listed as new.

Jeff Reisman

@Kaykay Might it not be easier to just use a report? Set the criteria to be a status of "new" and those are the only cities it will return.

Kaykay

@Jeff Reisman it's just part of a larger analysis that I will be doing in this new sheet, I will be setting up automated workflows which aren't supported in reports, and I cannot link cells from a report.

Jeff Reisman

To do what you want, you probably need to use IF with nested INDEX/MATCH formulas, and need to have some data you can match between the two sheets. So for instance if you have a "Row Number" column in both sheets, you can use something like this:

IF(INDEX({Sheet 1 Status Column}, MATCH([Row Number]@row, {Sheet 1 Row Number}, 0)) = "New", INDEX({Sheet 1 City Name}, MATCH([Row Number]@row, {Sheet 1 Row Number}, 0)), "")

In English: IF the status column on sheet 1, where the row number value is the same as the row number on this row here in sheet 2, equals "New", then populate this cell with the City Name value from Sheet 1 where the row number matches the row number for this row, otherwise leave it blank.

Then sort the sheet by the column containing your formula, to sort the blanks to the top or bottom of the sheet.

R. Smythe

New here, but I was just struggling with the same issue and leveraged Jeff's great advice into a more simple (for me) solution. I wanted to replicate a column from another sheet in order to make a lookup table, so I added a column to my lookup table with row number (just 1,2,3,4,5... all the way down) then made my formula:

=INDEX({Project Table Range 2}, CountRef@row, 2)

Where The Project Table range was the data I wanted to replicate (Project Name and auto-populated ProjectKey), CountRef was my new column in the lookup table, then "2" for the column to copy. Then I could just drag down as OP suggested.