Vlookup and IF Statement

[davewh]

I'm trying to automate a spreadsheet that calculates pay rates based on weekend weekday and public holidays. I have column A as the date starting 01/01/2019 Column B has the formula =TEXT(A1,"dddd") to add the day.

Column C is a Vlookup to a Public Holidays table using the formula =IF(ISNA(VLOOKUP(A1,$G$2:$I$17,3,FALSE))," ",VLOOKUP(A1,$G$2:$I$17,3,FALSE)). Column D is an IF Statement to pull Columns B & C together =IF(C1="Bank Holiday",C1,B1). there is then another formula that calculates the pay rate depending the day

Does anyone know if there is a way to combine the IF(ISNA formula in Column C with the IF statement in Column D?

Many Thanks
Davewh

 

[Joe4]

Firstly, assuming that you are not using a real old version of Excel, this formula:

[COLOR=#333333]=IF(ISNA(VLOOKUP(A1,$G$2:$I$17,3,FALSE))," ",VLOOKUP(A1,$G$2:$I$17,3,FALSE))

can be simplified to this:
[/COLOR]

[COLOR=#333333]=IFERROR(VLOOKUP(A1,$G$2:$I$17,3,FALSE),"")
[/COLOR]

So, couldn't you just use:

[COLOR=#333333]=IFERROR(VLOOKUP(A1,$G$2:$I$17,3,FALSE),B1)
[/COLOR]

 

[davewh]

Joe that's perfect thanks very much for the help

 

[Joe4]

You are welcome.
Glad I was able to help!