Search For Last Combination

[JHusk]

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]

Try this

Book1
	A	B	C
1	A1	A	D
2	A2	A4	D2
3	D1
4	A3
5	A4
6	D2
Sheet1

Cell Formulas
Range	Formula
B2:C2	B2	=LOOKUP(2,1/(LEFT($A$1:$A$6,1)=B1),$A$1:$A$6)

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C
1	A1	A	D
2	A2	A4	D2
3	D1
4	A3
5	A4
6	D2
7
8
Data

Cell Formulas
Range	Formula
B2:C2	B2	=XLOOKUP(B1&"*",$A$1:$A$10,$A$1:$A$10,"",2,-1)

 

[JHusk]

Try this

Book1
	A	B	C
1	A1	A	D
2	A2	A4	D2
3	D1
4	A3
5	A4
6	D2
Sheet1

Cell Formulas
Range	Formula
B2:C2	B2	=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]

How about

Fluff.xlsm
	A	B	C
1	A1	A	D
2	A2	A5	D3
3	D1
4	A3
5	A4
6	D2
7
Data

Cell Formulas
Range	Formula
B2:C2	B2	=B1&SUBSTITUTE(XLOOKUP(B1&"*",$A$1:$A$10,$A$1:$A$10,"",2,-1),B1,"")+1

 

[JHusk]

Another option

Fluff.xlsm
	A	B	C
1	A1	A	D
2	A2	A4	D2
3	D1
4	A3
5	A4
6	D2
7
8
Data

Cell Formulas
Range	Formula
B2:C2	B2	=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]

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

 

[JHusk]

How about

Fluff.xlsm
	A	B	C
1	A1	A	D
2	A2	A5	D3
3	D1
4	A3
5	A4
6	D2
7
Data

Cell Formulas
Range	Formula
B2:C2	B2	=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]

Glad we could help & thanks for the feedback.