Forum Discussion

noonah's avatar
noonah
Copper Contributor
Jan 30, 2020

Unable to Use Logic Functions to Pull Data from Different Sheets

Hi, 

 

I am creating a spreadsheet to track employee holidays. 

 

If the employee books the full day off, I want the cell under that date and by their name to read "Y".

If the employee books the morning off, I want the cell under that date and by their name to read "AM".

If the employee books the afternoon off, I want the cell under that date and by their name to read "PM"

 

 

 

I used the function IFS in cell D5 in the first image the following way: 

 

=IFS((Sheet1!C2:C500=January!D3)*AND(Sheet1!B2:B500="test@test.co.uk")*AND(Sheet1!D2:D500="Full Day"),"Y",(Sheet1!C2:C500=January!D3)*AND(Sheet1!B2:B1500="test@test.co.uk")*AND(Sheet1!D2:D500="AM"),"AM",(Sheet1!C2:C1500=January!D3)*AND(Sheet1!B2:B500="test@test.co.uk")*AND(Sheet1!D2:D1500="PM"),"PM")

 

But Cells D5:D500 just read "N/A" 

 

Am I using the wrong function for this or messing up the above formula? 

 

Thanks for your help

excel
Formulas and Functions

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 30, 2020

    noonah 

    In general IFS returns an array which fills all cells from D5 to D503 or so if you are on modern Excel, or first element of such array. Plus some incorrectness in formula, conditions are not met, thus N/A.

    I guess you'd like to find employee based on date and email address as ID, when it could be like

    =IFERROR(
   IF(
       LOOKUP(2,1/(Sheet1!C2:C500=January!$D$3)/(Sheet1!B2:B500="test@test.co.uk"))="Full Day",
       "Y",
       LOOKUP(2,1/(Sheet1!C2:C500=January!$D$3)/(Sheet1!B2:B500="test@test.co.uk"),Sheet1!D2:D500)
),"not found")

    (not tested)

Resources