Forum Discussion

clmcgreevy's avatar
clmcgreevy
Copper Contributor
Jan 06, 2020

Return one cell data in a second cell based on a third cell

Hi, 

I'm pretty sure this is a simple thing to do but I've been unable to find the answer via my internet searching so far.

I have a holiday leave sheet whereby column A shows the day leave is booked for, column E shows the amount of time for that day as each day has different working hours as below. This data is in a table in the spreadsheet at the moment and I add it manually.

Monday5.67
Tuesday5.87
Wednesday5.92
Thursday5.92
Friday5.83

 

I want cell E1 etc to automatically populate with the decimal hours based on what day is in cell A1. So, if cell A1 says Friday then cell E1 will say 5.83.

 

Like this:

DayDateTypeNumberDecimal hours
Friday01/01/2020BH15.83
     

 

How do I do this? I've tried If and lookup but I have no formal training so I've been finding my own way, sometimes successfully but not in this case!

 

Appreciate any help.

excel
Formulas and Functions

1 Reply

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

    clmcgreevy 

    For such your sample

    To avoid errors I'd recommend in column B use

    =C4

    and apply custom format "dddd"

    In F4 formula could be

    =IFNA(INDEX($I$3:$I$7,MATCH(TEXT($C4,"dddd"),$H$3:$H$7,0)),"")

    and drag it down. Sample file is attached.