Excel Magic Trick # 158: VLOOKUP w 2 Variables As Criteria

[ExcelIsFun]

You are welcome!

[caraes81]

Hi Fun Excel man.

How would you go about summing the return value of a criteria. The criteria repeats it self multiple times with different values. I would like to sum all this values.

Thanks

Great stuff

[ExcelIsFun]

Try this video title:

Excel Magic Trick 320: Lookup Adding: SUMPRODUCT & SUMIF

[skiNchocolate]

Hey can you help me with EXCEL...I.T

[SupremeParis]

How would you go about adding in a 3rd variable? This worked perfectly for a while but now a 3rd variable needs to be added.

Help pls :)

[ExcelIsFun]

Dear SupremeParis,

I would switch to a different tactic (not VLOOKUP). See this Playlist for various methods:

Various Excel Extract Records From Database Table / List

If you do not know how to find the Playlists, search for and watch this video title:

Search For Excel Videos Download Excel Files excelisfun

--excelisfun

[ssalazarjunior]

I apologize. I have 4 column: User,Store,Login,Logoff. When an employee clocks in they get a day/time stamp in a cell under Login and same w/clock out and Logoff. My report groups this info by the employee under User. If they clock in, then out for lunch, then in when they return, and out when they leave, i have two rows with the same day. So its difficuld to reference the day. I'm using VLOOKUP looking for the day +1 so it returns the later of the times, how can i get the first clock in? thanks

[ExcelIsFun]

Dear ssalazarjunior,

VLOOKUP looking for the day +1 is very clever to get the second day, but cant you just use the VLOOKUP without the +1 for the first date?

--excelisfun

[ExcelIsFun]

Dear ssalazarjunior,

Or maybe you can just have a one record (one line) for each employee for each day and have the Time In Before Lunch, Out Before Lunch, In After Lunch, Out After Lunch, then a formula at the end to get the time worked?

--excelisfun

--excelisfun

[ssalazarjunior]

This video helped me check my employees time clock compared to there schedule, but now i'm running across the problem when they clock out for lunch. I can't seem to figure it out. I have it look up the day plus one so it returns the highest value thats less then the next day, but if they clock out for lunch, i cant return it the opposite way. Any ideas?

[ExcelIsFun]

Dear ssalazarjunior,

I do not understand your question or how your data is setup.

--excelisfun