Need help with MATCH

KDTMiller

Hi there!

Please help!

I have a distribution list (Names of Users) that is feeding into different trackers.

I am using an auto row column labeled "ID" and a helper column labeled "RowNum" with this formula =MATCH(ID@row, ID:ID, 0).

The issue is that I completely missed a user and so I made the mistake of entering a row in the middle so that I could put him in alphabetical order.

That screwed some things up and now I'm wondering how can I make it so that if new users are added, regardless of order, it won't mess things up?

KDTMiller

Hi all, sorry this is my first time asking a question and I actually had two separate questions and labeled this incorrectly and did not mean to submit this one (I wanted to save as draft). I have already edited the post to reflect my first question about getting MATCH using auto-number column to work even if entries are put in after the fact and in the middle of previous entries. It looks like the edit needs to be approved, so sorry if this doesn't make sense!

AravindGP

Hi @KDTMiller

The issue with using an auto number column is that when you add a new row, (through an insert as well) the auto number field will add the next number in the sequence and when the numbers are not in sequence in the look up, it tends to not work as expected. You have two options to fix this. Either sort the sheet's auto number column in ascending order or convert the auto number column as text/number field, save, delete all the data, save, convert the column to auto number again with the starting number as 1 and save to refresh the column.