Forum Discussion
Finding the next value in list - nothing to match off of
Hi Everyone, Bit of a tricky one but I'll do my best to explain my issue. I have a list starting at B1 to B26. B1 is "A-A", B2 is "A-B", B3 is "A-C" etc. all the way to "A-Z". In A1 I want t...
I feel I'm missing a detail but I'll give this a shot.
Presuming the data looks like this with jumbled values in B:
You might create an XMATCH, an INDEX, or whichever lookup you need using some sequencing:
=LET(n,"A-"&CHAR(SEQUENCE(26,,65,1)),XMATCH(n,B1:B26))
Patrick2788 Hi Patrick, thank you for trying to help, unfortunately the formula does not work when there are duplications. I'm trying to attach a sample as you have done but I cannot work out how to attach. Would you mind sending me an email on pauljbrag"@"gmail"dot"com and I'll mail you what I'm working with?
- are you looking for a rank order of the text strings in col B or those values sorted or something else?
for example could you use =SORT(B1:B26)
if it is a ranking you need I have a couple questions like will it only change the last character or could both change? - I recommend sharing the sample via OneDrive or another host. You may also send the file via private message.
This formula will work for column P.
=LET(demo,IF(G5="ladies","womens",IF(G5="pre-boys","toddlers",G5)),XLOOKUP($K5,WORKINGS!$A$2:$A$55,XLOOKUP(demo&"*",WORKINGS!$L$1:$P$1,WORKINGS!$L$2:$P$55,"",2)))Uploading the workbook in case there's more and I'm not able to get to it.
