Forum Discussion
Solved
Trying to use IFS formula to create a Data Validation field
Hello, I'm trying to use the IFS field to create a data validation entry where it picks the list for the 2nd column based on the selection in the first column. I was able to successfully do this for...
- So I discovered something that I'm leaving here in case anyone else has the same problem.
The issue wasn't the IFS statement but that I was trying to use it in the Source field in Data Validation. Apparently that field can only hold 256 characters. I tried to short the names of the tab and the lists but it wasn't enough. I played around with it all a bit more and realized that I can name Formulas. So, I created my entire IFS statement as a FORMULA, named that, and then in the data validation, just called to the formula. It works, I was able to use full names and didn't have to shorten anything.
Can you please help me with this? These, (Acquiring, Applying, Leveraging, and Influencing) are used in a validation list and if one of these are selected, I want it to display the percentage in the cell next to it
=IF(D5=Acquiring, "25%",D5="Applying", "50%",D5=Leveraging, "75%",D5="Influencing", "100", "")
Your formula could be
=IF(D5=Acquiring, 25%, IF(D5="Applying", 50%, IF(D5="Leveraging", 75%, IF(D5="Influencing", 100%, ""))))
or
=IFS(D5=Acquiring, 25%, D5="Applying", 50%, D5="Leveraging", 75%, D5="Influencing", 100%, TRUE, "")
Alternatively, create a small lookup table:
In this screenshot, the lookup range is J2:K5. You can then use
=XLOOKUP(D2, $J$2:$J$5, $K$2:$K$5, "")
or
=IFERROR(VLOOKUP(D2, $J$2:$K$5, 2, FALSE), "")