Welcome to Excel Jet blog post. In this tutorial, we will learn how to use IFS function inside Data Validation.
Data Validation is a feature in Excel that allows users to control values entered in the spreadsheet.
In the caption below, we want to apply List Data Validation to cells B5:B8 using lookup table to the right as the input values of the IFS formula.
Note: SUMIFS formula is applied to cells C5:C8 which invariably implies that when we select a particular State in cells B5:B8, Total Sales will be displayed
To apply List Data Validation:
- Select cells B5:B8
- In the Data Tools group of the Data Tab, click on Data Validation
- Select List from the Allow drop-down
- In the Source cell reference box, execute the formula:
=IFS(A5=”Stephen Charles”,$F$2:$F$4,A5=”Yvonne Matthew”,$G$2:$G$4,A5=”Phoebe Harding”,$H$2:$H$5,A5=”Connor Rulibeth”,$I$2:$I$5)
From the drop-down List in cells B5:B8, select the following States as follows Abuja, Kaduna, Edo and Osun States respectively to see the Total Sales.
Task completed successfully!