CODEWITHSUNDEEP
excelexcel-formulanested
djoins
djoins

Reputation: 15

How to nest multiple if functions to result in different vlookups in Excel?

I am trying to auto populate start dates for a specified time frame based on 2 sets of criteria. Criteria 1 is the name of the time frame, in my example "Period 1, Period 2". Each Period can have different starting days which would be criteria 2 "Monday, Tuesday". I have tables set up with specified dates that correlate to the starting day of the week. this table is named "_effectivedates".

Essentially what I am wanting to achieve would be: If B11 = Monday then look up starting date that matches "Period 1" in B17 from _effectivedates column 2. IF B11 = Tuesday then then look up starting date that matches "Period 1" in B17 from _effectivedates column 4.

Below is the formula I have tried, it seems to work fine with one if B11 = Monday, but if i change B11 to read Tuesday it returns "false"

=IF(B11="Monday",VLOOKUP(B17,_effectivedates,2,IF(B11="Tuesday",VLOOKUP(B17,_effectivedates,4,))))

Upvotes: 0

Views: 99

Answers (2)

Isolated
Isolated

Reputation: 6454

It's returning "False" when Tuesday because you didn't close your parenthesis after you first vlookup. The formula should be:

=IF(B11="Monday",VLOOKUP(B17,_effectivedates,2),IF(B11="Tuesday",VLOOKUP(B17,_effectivedates,4)))

Upvotes: 1

Andreas
Andreas

Reputation: 23968

What you could do is a translation table of Monday = 2 and Tuesday = 4 and so on and use vlookups to know what value to use.

Somewhere in the workbook:

A        B
Monday   2
Tuesday  4

And so on...

Then your vlookup will be:

=Vlookup(B17,_effectivedates,Vlookup(B11,[range above],2, false), false)

So you vlookup the weekday and use that in the vlookup.

Upvotes: 1

Related Questions