Creating a formula for auto text

[ChristineC]

Hello,
I am trying to set up auto text based off a value in a cell where there could be multiple values...

For Example in column D the cell will either have a "T", "G", or "N"... and I am trying to have auto text (for T=94010 and for G=36941) populate into another column. (Don't care about any other values at this point)

I've managed to do a If formula for the T value but cannot figure out how add on the G value this way. Some googling has come up with VLOOKUP ideas but I'm so lost with that as well. I really aughtta take a refresher course...

 

[dreid1011]

A few different options to see how to achieve multiple results:

Book1
	D	E	F	G	H
1		IFS()	LOOKUP()	Nested IF()	SWITCH()
2	T	94010	94010	94010	94010
3	G	36941	36941	36941	36941
4	N	77001	77001	77001	77001
5	V	#N/A	94010
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=IFS(D2="T",94010,D2="G",36941,D2="N",77001)
F2:F5	F2	=LOOKUP(D2,{"G","N","T"},{36941,77001,94010})
G2:G5	G2	=IF(D2="T",94010,IF(D2="G",36941,IF(D2="N",77001,"")))
H2:H5	H2	=SWITCH(D2,"T",94010,"G",36941,"N",77001,"")

Notice with IFS() you get #N/A if the value is anything other than your accepted values. You could add a condition for blank, but anything else and you would need to wrap it IFERROR or IFNA or something like that.

With LOOKUP(), if the value does not match one of the accepted values, it chooses the next closest value alphabetically. Again, you could put in a condition for blank, but that is about it.

The nested IF() and SWITCH() formulas give you a little bit more control to handle values either blank or a different letter.

 

[Scott Huish]

Using a more modern function, because it has a Not Found parameter.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	T	94010									T	94010
2	N	10000									G	36941
3	G	36941									N	10000
4	A
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=XLOOKUP(A1,$K$1:K3,$L$1:L3,"",0)

 

[ChristineC]

A few different options to see how to achieve multiple results:

Book1
	D	E	F	G	H
1		IFS()	LOOKUP()	Nested IF()	SWITCH()
2	T	94010	94010	94010	94010
3	G	36941	36941	36941	36941
4	N	77001	77001	77001	77001
5	V	#N/A	94010
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=IFS(D2="T",94010,D2="G",36941,D2="N",77001)
F2:F5	F2	=LOOKUP(D2,{"G","N","T"},{36941,77001,94010})
G2:G5	G2	=IF(D2="T",94010,IF(D2="G",36941,IF(D2="N",77001,"")))
H2:H5	H2	=SWITCH(D2,"T",94010,"G",36941,"N",77001,"")

Notice with IFS() you get #N/A if the value is anything other than your accepted values. You could add a condition for blank, but anything else and you would need to wrap it IFERROR or IFNA or something like that.

With LOOKUP(), if the value does not match one of the accepted values, it chooses the next closest value alphabetically. Again, you could put in a condition for blank, but that is about it.

The nested IF() and SWITCH() formulas give you a little bit more control to handle values either blank or a different letter.

This did the trick, thank you!

 

[ChristineC]

Using a more modern function, because it has a Not Found parameter.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	T	94010									T	94010
2	N	10000									G	36941
3	G	36941									N	10000
4	A
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=XLOOKUP(A1,$K$1:K3,$L$1:L3,"",0)

Thank you for your help too.. I will look into this option for future too!

 

[dreid1011]

You're welcome, and happy to help.