Aug 03 2020 12:09 PM
Hello, I could use some help with the IFS formula. Thanks in advance!
I am using Excel as part of Microsoft Office Professional Plus 2013. I have two spreadsheets and I am trying to pull data from a tab on one spreadsheet (DailyRollup.xlsx, the "DailyExtract" tab) to be shown on a different spreadsheet (Forecast.xlsx) if one of four criteria are met, otherwise leaving the field blank.
Each cell in Column E of the source file either has one of four codes in it: P, H, T, or S, or the field is blank if none of those apply. The source cells are hard-coded with those letter codes. The data I need to show in my "Forecast.xlsx" spreadsheet starting in Cell D3 comes from the source document in Cell E1380 is the first instance of the codes I am trying to
When I use the formula shown below in Cell D3 of my Forecast.xlsx document, I get the #NAME? error.
=IFS('[DailyRollup.xlsx]DailyExtract'!$E1380="P","P",'[DailyRollup.xlsx]DailyExtract'!$E1380="H","H",'[DailyRollup.xlsx]DailyExtract'!$E1380="T","T",'[DailyRollup.xlsx]DailyExtract'!$E1380="S","S",TRUE,"")
Aug 03 2020 12:33 PM
SolutionThe IFS function is not available in Excel 2013, only in Excel 2019 and Excel if Microsoft 365 (aka Office 365). So you'll have to use nested IF functions:
=IF([DailyRollup.xlsx]DailyExtract!$E1380="P","P",IF([DailyRollup.xlsx]DailyExtract!$E1380="H","H",IF([DailyRollup.xlsx]DailyExtract!$E1380="T","T",IF([DailyRollup.xlsx]DailyExtract!$E1380="S","S",""))))
Aug 03 2020 12:48 PM
@Hans Vogelaar Thank you for that, the nested IF function worked just fine. Appreciate your help :)
Aug 03 2020 01:05 PM
As variant
=IF(SUM(COUNTIF([DailyRollup.xlsx]DailyExtract!$E1380,{"P","H","T","S")),[DailyRollup.xlsx]DailyExtract!$E1380,"")
Aug 03 2020 12:33 PM
SolutionThe IFS function is not available in Excel 2013, only in Excel 2019 and Excel if Microsoft 365 (aka Office 365). So you'll have to use nested IF functions:
=IF([DailyRollup.xlsx]DailyExtract!$E1380="P","P",IF([DailyRollup.xlsx]DailyExtract!$E1380="H","H",IF([DailyRollup.xlsx]DailyExtract!$E1380="T","T",IF([DailyRollup.xlsx]DailyExtract!$E1380="S","S",""))))