Check result of formula and change to a list value

[ollyhughes1982]

Hi,

I have a pool league results card that I complete each week, for my team’s personal use and records. This is fine, but in this I use our ‘short’ names, rather than our official full names. See image below:

However, when I post the result card to the league, I’d like it show our full names. Is there a way in Excel that I could have a version where the formulas in each cell checks the result and then a further part of the formula chooses the correct full name from a lookup list? I’m aware that this is a little odd, as it is a formula that will look at the result within the same formula to then do a lookup and change the value.

e.g. Olly should change to Oliver Hughes, Stuart to Stuart Bloggs, Mike to Michael Jones and so on...

I always record the full names of opposition anyway, so these won't need changing.

File is here: Pool Team (Division 1, 2023-2024) WORKING.xlsx

Thanks in advance!

Olly.

 

[Jeffrey Mahoney]

In this example, each player has a Player ID

Cell B1 is the choice to show short or full names which changes all the results throughout the workbook. Range A4:B4 is the working example. Range I1:K4 is the player table

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		Full							Player ID	Short Name	Full Name
2									1001	Olly	Olliver Hughes
3									1002	Stuart	Stuart Bloggs
4	1002	Stuart Bloggs							1003	Mike	Michael Jones
5
6
Sheet2

Cell Formulas
Range	Formula
B4	B4	=VLOOKUP(A4,PlayerTbl,IF(NameSelection="Short",2,3),FALSE)

Named Ranges
Name	Refers To	Cells
NameSelection	=Sheet2!$B$1	B4
PlayerTbl	=Sheet2!$I$2:$K$4	B4

Cells with Data Validation
Cell	Allow	Criteria
B1	List	Short,Full

 

[ollyhughes1982]

In this example, each player has a Player ID

Cell B1 is the choice to show short or full names which changes all the results throughout the workbook. Range A4:B4 is the working example. Range I1:K4 is the player table

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		Full							Player ID	Short Name	Full Name
2									1001	Olly	Olliver Hughes
3									1002	Stuart	Stuart Bloggs
4	1002	Stuart Bloggs							1003	Mike	Michael Jones
5
6
Sheet2

Cell Formulas
Range	Formula
B4	B4	=VLOOKUP(A4,PlayerTbl,IF(NameSelection="Short",2,3),FALSE)

Named Ranges
Name	Refers To	Cells
NameSelection	=Sheet2!$B$1	B4
PlayerTbl	=Sheet2!$I$2:$K$4	B4

Cells with Data Validation
Cell	Allow	Criteria
B1	List	Short,Full

I'll give this a try. Thanks

 

[ollyhughes1982]

I'm not sure if this is what I'm after. i want the formula in G14, G16, G18 etc. to be able to look at what the result of the formula is and then lookup from the 'Registration Details - 23-24' worksheet what the full name should be and display that.

 

[Peter_SSs]

i want the formula in G14, G16, G18 etc. to be able to look at what the result of the formula is and then lookup from the 'Registration Details - 23-24' worksheet what the full name should be

G14 contains "Olly". I don't see anything on 'Registration Details - 23-24' that logically links the name "Olly" to any of the 9 names listed on that worksheet. Even less for "Daz" in G22.

 

[ollyhughes1982]

G14 contains "Olly". I don't see anything on 'Registration Details - 23-24' that logically links the name "Olly" to any of the 9 names listed on that worksheet. Even less for "Daz" in G22.

Apologies, I thought I had uploaded a new version with this mapped in. I have now added this.

You can now find this updated file at the same link. I think I have now solved this with the formula you will be able to see in G14 and AT14, shown below:

G14:

=XLOOKUP(IF('Match Card # 1'!$A$3="Home",'Match Card # 1'!B4,'Match Card # 1'!H4),'Registration Details - 23-24'!$AD$10:$AD$18,'Registration Details - 23-24'!$J$10:$J$18,IF('Match Card # 1'!$A$3="Home",'Match Card # 1'!B4,'Match Card # 1'!H4))

AT14:

=XLOOKUP(IF('Match Card # 1'!$A$3="Away",'Match Card # 1'!B4,'Match Card # 1'!H4),'Registration Details - 23-24'!AD10:AD18,'Registration Details - 23-24'!J10:J18,IF('Match Card # 1'!$A$3="Away",'Match Card # 1'!B4,'Match Card # 1'!H4))

Thanks.

 

[Peter_SSs]

Apologies, I thought I had uploaded a new version with this mapped in. I have now added this.

You can now find this updated file at the same link. I think I have now solved this ..

In that case could you use these considerably shorter formulas?
G14:

=LET(n,INDEX('Match Card # 1'!B4:H4,1+6*('Match Card # 1'!$A$3="Away")),lur,'Registration Details - 23-24'!J10:AD18,XLOOKUP(n,TAKE(lur,,-1),TAKE(lur,,1),n))

AT14:

=LET(n,INDEX('Match Card # 1'!B4:H4,1+6*('Match Card # 1'!$A$3="Home")),lur,'Registration Details - 23-24'!J10:AD18,XLOOKUP(n,TAKE(lur,,-1),TAKE(lur,,1),n))

 

[ollyhughes1982]

In that case could you use these considerably shorter formulas?
G14:

=LET(n,INDEX('Match Card # 1'!B4:H4,1+6*('Match Card # 1'!$A$3="Away")),lur,'Registration Details - 23-24'!J10:AD18,XLOOKUP(n,TAKE(lur,,-1),TAKE(lur,,1),n))

AT14:

=LET(n,INDEX('Match Card # 1'!B4:H4,1+6*('Match Card # 1'!$A$3="Home")),lur,'Registration Details - 23-24'!J10:AD18,XLOOKUP(n,TAKE(lur,,-1),TAKE(lur,,1),n))

Thanks

 

[Peter_SSs]

You're welcome.