Drop down list with selection containing Alpha and Numeric characters not working

[Macca1962]

Hi, Is it possible to select data from a drop down list containing both alpha and numeric characters if they are all not in the exact same format

I am trying to pull some statistics from a football competition that had two seasons in one year (2022) and tried to differentiate them by using 2022 for the first season in 2022 and 2022B for the second season.

My list has

Option 1
2017 - corresponds to Season 1 or 2017A below
2018 (corresponds to Season 2 or 2018A below)
2019 (corresponds to Season 3 or 2019A below)
2020 (corresponds to Season 4 or 2020A below)
2021 (corresponds to Season 5 or 2021A below)
2022 (corresponds to Season 6 or 2022A below)
2022B (corresponds to Season 7 or 2022B below)
2023 (corresponds to Season 8 or 2023A below)

If i use the drop down list above it will only extract data for 2022B from the drop down list. All other values do not show any data but do NOT display any error messages either.

However if I change the data to the following I can extract the data for all Seasons 1 through 8

Option 2
Season 1
Season 2
Season 3
Season 4
Season 5
Season 6
Season 7
Season 8

and if I change the data to the following I can extract the data for all Seasons 2017A through 2023A

Option 3
2017A
2018A
2019A
2020A
2021A
2022A
2022B
2023A

Screenshots 1 (source data), 2 and 3 are for Option 1. Screenshot 2 is what occurs for 2017, 2018, 2019, 2020, 2021, 2022 and 2023. Screenshot 3 is what occurs for 2022B (this is what it should display).

Screenshots 4 (source data) and 5 are for Option 2. Screenshot 5 is what occurs for Season 1 through Season 8 (this is what it should display)

Screenshots 6 (source data) and 7 are for Option 3. Screenshot 7 is what occurs for 2017A, 2018A, 2019A, 2020A, 2021A, 2022A, 2022B and 2023A (this is what it should display)

Note - I only have formulas for Allan, Sarah in Screenshots 2, 3, 5 and 7 (I wanted to see if this will work with Option 1 before copying the formulas to other lines)

I just need some way to differentiate between Seasons 2022 and 2022B.

I am happy enough to use the Option 3 if this is a shortcoming of Excel (although this is going to cause a lot of pain adjusting the data in numerous spreadsheets)

I am sure it is not my formula as it is extracting all the data I need for Option 2 and Option 3 (all items in the drop down list) and only for 2022B in Option 1 (this proves the formula works so I think it might be because the data in the drop down list is not the exact same format for every line).

Hoping someone can confirm if Option 1 is possible and if so how I can get it to work.

Regards, Andrew

 

[myall_blues]

I am sure it is not my formula

Nevertheless, it would be helpful to see your formula.

It's possible to do.

Book1
	A	B	C	D	E	F	G	H
1	Data	Result					Dropdown	Result
2	2017	1					2018	2
3	2018	2					2022B	7
4	2019	3
5	2020	4
6	2021	5
7	2022	6
8	2022B	7
9	2023	8
10
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=XLOOKUP(G2,$A$2:$A$9,$B$2:$B$9)

Cells with Data Validation
Cell	Allow	Criteria
G2:G3	List	=$A$2:$A$9

 

[Macca1962]

Thanks Murray. I appreciate your reply. I have been performing a lot of testing on this and the only way I have been able to get this to work is by changing the years to 2017A, 2018A, 2019A, 2020A, 2021A, 2022A, 2022B (for the year that had two seasons in it). I still had trouble extracting the data using xlookup the table reference . Eventually I tried using the absolute cell reference range instead of the table reference and it worked. Sorry it took so long getting back to you but it looks like I have managed to get it to work now. Cheers Andrew

 

[Macca1962]

Thanks Murray. I appreciate your reply. I have been performing a lot of testing on this and the only way I have been able to get this to work is by changing the years to 2017A, 2018A, 2019A, 2020A, 2021A, 2022A, 2022B (for the year that had two seasons in it). I still had trouble extracting the data using xlookup the table reference . Eventually I tried using the absolute cell reference range instead of the table reference and it worked. Sorry it took so long getting back to you but it looks like I have managed to get it to work now. Cheers Andrew

 

[myall_blues]

You’re welcome. Glad you were able to get it sorted out.