Help With Index and Match


I have two sheets, one is for raw data to be updated up to 10 times a day and the other to visually see the results of formulas looking up the data from the raw data sheet.

I need to find a way to lookup an employee's user name on the dashboard, find the number of minutes between the last scan time/start of break and the end of break/first scan time after breaktime in the raw data, then add up the total time taken for break on the dashboard.

Break is from 12:55 to 13:35.

I set up break start and end times in a couple of columns on the raw data page so i could run formulas off of them. I subtracted the employee scan from the start or end of break to get a number of minutes and have been trying to lookup the user name from the dashboard page to find the min() value in the applicable column on the raw data page.

Any help is appreciated!




I'm not entirely clear about what you want (and your worksheets are protected) but to my limited understanding of your problem it should be solvable with VLOOKUP. Please consider this formula as an example.

[Dashboard!B3] =VLOOKUP($A3,Scans!$A$2:$G$1000,2,FALSE)

I would urge you to assign a name to Scans!$A$2:$G$1000 and use that name in the formula. In that way, when the range changes you only modify the target associated with the name instead of a million formulas all over your workbook. Saves time and eliminates a source of errors.

Your big problem seems to be your understanding of times in Excel. A formula like =TEXT(C3-B3,"HH:MM:SS") displays the time as you want it but it is useless for calculations because it is text. In Excel times are numbers, and numbers can be used in calculations. Take your columns A and B on the Scans tab. Column B contains a number like 4359.583214 (format the cell as General to make it visible). This number is formatted as Date/Time with a cell format mask like m/d/yyyy hh:mm. You didn't do that, Exel did. You tried to change this format to h:mm AM/PM. That didn't work because of your sheet's protection. Users aren't allowed to Format cells. So you created column B. Actually, A and B have exactly the same value but formatted differently - or should have. But in this process you changed the value from number to text. Therefore column B is useless for calculation.

In a number like 4359.583214 the integer indicates the day. Time is indicated as a fraction of a full day to the right of the decimal point - down to a nano-second. If you ever need to calculate a break that occurs over midnight you can't do it unless your time values include the day. They shouldn't bother you and you can hide them from view by applying a suitable time format. However, if you wish to extract the time only from a date/time value use a formula like this (where the Date/Time value is in A3).


Answer the Question

You must create an account to use the forum. Create an Account or Login