Using IFS Function Inside Data Validation

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)

Click OK

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!