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.

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
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, 2022 at 4:50
  • Thank you so much! This has worked perfectly!
    – Jack Mottershaw
    Jun 23, 2022 at 8:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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