0

I'm looking to find a formula which I can use, to show, the next lowest available number for a range of Asset Tag 1 - 500

As an example if 1 has been used the next available number will be 2.

However, if 1 is used and then 3 is used, the next lowest available will still be 2.

Any help would be much appreciated.

New contributor
Jack Mottershaw is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

1 Answer 1

0

Given the list of used Asset Tags in column A (as pure numbers), the following in B1 will give the lowest available tag:

=min(unique({sequence(500);A1:A},,1))

What is happening here is that you are creating a literal array (curly brackets) containing the set of 500 Tags in a column, with your list of used tags appended beneath it. UNIQUE with the optional 'only once' argument will return all the tags which appear in the combined list only once, i.e. the ones which haven't been used already. MIN then simply returns the lowest tag in this list.

2
  • Good idea. But if the elements of column A were more than 499, your formula would return 0, so I propose this fix: =min(unique({sequence(count(A1:A)+1);A1:A},,1))
    – Daniele
    Jun 23 at 4:50
  • Thank you so much! This has worked perfectly! Jun 23 at 8:50

Your Answer

Jack Mottershaw is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.