Forum Discussion
cTennant2
Sep 13, 2024Copper Contributor
How to return the next value in a row based on whether it is already used in a list
Hi I have 2 sheets as below, In sheet 1, when a user selects 'RED' in ID type, they will be prompted to enter 'original ID number'. I then need it to check the 'original id number' against the 'ID ...
cTennant2
Copper Contributor
Thanks you so much for your reply, its really appreciated. I'm restricted from using macros however and trying to achieve this with index and match formulas if at all possible. Do you know if this is possible, i've tried lots of combinations but just can't seem to get it right. Many thanks:-)
HansVogelaar
Sep 18, 2024MVP
Enter the starting value ID0001 in A2.
In A3:
=IF(C3="RED", LET(v, XLOOKUP(B3&"*", A$2:A2, A$2:A2, "", 2, -1), B3&"-"&IFNA(TEXTAFTER(v, "-"), 0)+1), "ID"&TEXT(MAX(--MID(A$2:A2, 3, 4))+1, "0000"))
Fill down.