Nested IF+vlookup

[Vegahorror]

Hi,

I am new to this excel world and I have a question about an If formula I want to make.

I have a list with associates ids and schedule based Mon-Sun, I need to pull out from our system the time they actually arrived and then compare it with the schedule to know if they were late, early, or abs and if late, how much time.Then I put all in a pivot.

I have a column that gives me which day of the week the date represents, then I need to do a Nested IF with a vlookup: IF(X2=$AJ$4,VLOOKUP(A2,$AI$5:$AW$16,2,IF(X2=$AL$4,VLOOKUP(A2,$AI$5:$AW$16,4)))) X2 has the week day (Mon,Thu,...) $AJ$4 is the schedule table I have were $AJ$4 should be Mon and $AL$4 should be Tue, then the vlookup looks for the ID in the schedule table and depending on the day should give me the column with the schedule (ex 6:00 am for Mon). The first part of the formula works fine if its Mon and returns the expected schedule 6:00 am, but when it goes to Tue or Wed it gives me False result.

I plan to set one IF+vlookup per day of the week, but I want the result to always be the value on the schedule table for each day (ex Mon=6:00 am, Tue=OFF)

Thanks for all the help you guys can provide

 

[hdbkiwi]

Hey Vegahorror,

I would really try and learn how to use Index Match instead of Vlookup - it is the exact same function but is a way better formula and avoids all sorts of errors that vlookup returns.

One common mistake I used to make when I was still using vlookup was when I copied and pasted my formula to a new cell it updates the column index - is that happening with you? Make sure what you want to stay fixed is fixed and what you want changed is changing in the right way. Again, try it with index match and see where you get to, it should work!