Jan 04 2021 12:01 PM
Jan 04 2021 12:01 PM
Hello i am trying to do a vlookup across multiple sheets that have the same information, so basically i'm trying to see how many days an employee has missed, so there is data for employees who have missed two, three, four and six days . If an employee missed six days they will also be on the two ,three, four. days missed data but i want it to return six days only. I usually do a vlookup four times, is there a way i can use one formula so i started from the two days missed tab and i did a vlookup against the six, four and three days tab. So i want excel to tell me the maximum number of days an employee has missed by cross referencing each sheet, i checked google and i found the IF error and Vlook up combination but when i tried it , the data it returned was not accurate , this was the formula (=IFERROR(VLOOKUP(B2,six!B:C,2,FALSE),IFERROR(VLOOKUP(B2,FOUR!B:C,2,FALSE),IFERROR(VLOOKUP(B2,THREE!B:C,2,FALSE),IFERROR(VLOOKUP(B2,TWO!B:C,2,FALSE), "Not Found"))))
If there are other ways i can arrive at my answer without using vlookup, kindly let me know also as im trying to cut the manual steps it takes it in doing the vlookup against each spreadsheet.
Jan 04 2021 05:44 PM
I realize that the actual spreadsheet almost certainly contains the real names of real people, as well as perhaps other confidential information. If there is any possibility of your creating a facsimile of it that uses, say, the names of Star Wars or Disney characters in place of the actual names, it would really be helpful to see how you've organized the data. In particular, I wonder why you seem to think it necessary to have multiple sheets with redundant (or so it would seem) data.
My very frank reaction on reading your description is to think you probably would benefit from re-thinking the basic design here. Excel really is good at taking single databases (in the form of a well-designed Excel Table), for such things as employee attendance records, and then producing the kind of report you're trying to create. I say this as a person who was the director of the HR/Payroll database for a major corporation (I'm now long retired).... I've worked with this kind of data for decades...what you describe sounds as if you've taken what used to be a method of keeping records on paper and moved it to Excel, hoping to take advantage of Excel's magic to do some of the review and summarization of attendance logs, but maybe you didn't really re-think the method of keeping the raw data in the first place.
If you're open to that kind of re-thinking, in the long run you'd benefit. Creating a formula that can navigate a poor design [if my impression is in any way correct] may be possible, but it's the equivalent of just putting a larger bandage on an infection, rather than treating the underlying cause.
Jun 01 2021 01:49 PM