# Search For Last Combination



## JHusk (Dec 28, 2022)

How would I find the last combination of a letter and number in a list?

A = Combinations
B = Results for A
C = Results for D

A1
A2
D1
A3
A4
D2
and so on...(not a pattern, just the result of previous selections).

So I would need Column B to return the last combination starting with "A", which would be A4. And Column B would return the last combination starting with "D", which would be D2.


----------



## Phuoc (Dec 28, 2022)

Try this

Book1ABC1A1AD2A2A4D23D14A35A46D2Sheet1Cell FormulasRangeFormulaB2:C2B2=LOOKUP(2,1/(LEFT($A$1:$A$6,1)=B1),$A$1:$A$6)


----------



## Fluff (Dec 29, 2022)

Another option
Fluff.xlsmABC1A1AD2A2A4D23D14A35A46D278DataCell FormulasRangeFormulaB2:C2B2=XLOOKUP(B1&"*",$A$1:$A$10,$A$1:$A$10,"",2,-1)


----------



## JHusk (Dec 29, 2022)

Phuoc said:


> Try this
> 
> Book1ABC1A1AD2A2A4D23D14A35A46D2Sheet1Cell FormulasRangeFormulaB2:C2B2=LOOKUP(2,1/(LEFT($A$1:$A$6,1)=B1),$A$1:$A$6)


This works perfectly. Now what if I wanted to know what the next number in either sequence would be? Meaning, the returned results of the formula are A4 & D2 respectively, so the next numbers available in each sequence would be A5 & D3, respectively. How would I find those?


----------



## Fluff (Dec 29, 2022)

How about
Fluff.xlsmABC1A1AD2A2A5D33D14A35A46D27DataCell FormulasRangeFormulaB2:C2B2=B1&SUBSTITUTE(XLOOKUP(B1&"*",$A$1:$A$10,$A$1:$A$10,"",2,-1),B1,"")+1


----------



## JHusk (Dec 29, 2022)

Fluff said:


> Another option
> Fluff.xlsmABC1A1AD2A2A4D23D14A35A46D278DataCell FormulasRangeFormulaB2:C2B2=XLOOKUP(B1&"*",$A$1:$A$10,$A$1:$A$10,"",2,-1)


This works great, exactly as I was asking for. Curious of your insight regarding the additional question of finding what would be next number in the given sequences.


----------



## Fluff (Dec 29, 2022)

There was no insight, I just modified the formula I suggested in post#3 to match the new requirement in post#4.


----------



## JHusk (Dec 30, 2022)

Fluff said:


> How about
> Fluff.xlsmABC1A1AD2A2A5D33D14A35A46D27DataCell FormulasRangeFormulaB2:C2B2=B1&SUBSTITUTE(XLOOKUP(B1&"*",$A$1:$A$10,$A$1:$A$10,"",2,-1),B1,"")+1


This did the exact extra lookup I was hoping for. I wish I could have 24 hours with your brain lol

As always, thank you so very much for your help...


----------



## Fluff (Dec 30, 2022)

Glad we could help & thanks for the feedback.


----------

