Nested VLOOKUP & SUMIF in IF STATEMENT with several variables

This is my first post - so please forgive me if I'm missing any pertinent info! I've attached an example of my workbook to help make sense of what I'm trying to do...

Example.xlsm

Basically, I pull a report from our database and import it into a new sheet (the sheet titled "Load Hrs" in my example) in an existing workbook that already contains a sheet with a table of data (the sheet titled "Faculty Seniority List in my example.) Right now, I have inserted four new columns into my imported sheet & each column has its own formula to get the results I'm looking for, but I know there HAS to be a way to write a formula that would combine the 4 formulas in each column into 1 formula. I suspect this would involve a nested vlookup & sumif into a pretty hefty IF statement, but I'm somewhat of a novice at complex formulas and I'm hoping someone can help!

So here's what I did:

1. In column J, I want to look up the last name (cell F2) in Table3 to find the faculty member's status and display their status in J2 (however I'm running into a problem that I'm not sure how to fix when there are two people with the same last name - i.e. Cole, Paul vs. Cole, Chuck)

2. In column K, I want to look up every occurance of the last name (cell F2) and display the sum of all of their contact hours (in this example I don't have the same problem of two people with the same last name, however I do anticipate that problem in the future - so I'd need to figure out how to write this into the formula)

3. I then inserted columns L & M as a means to get the final results I wanted in column N - only because I couldn't figure out any other way (although I know there is one!) So columns L & M are looking up the status that's displayed in J2 in Table4 in order to display the minimum and maximum hours attributed to that status --> all so I could write the formula in column N to find out if the faculty member's total contact hours are within the allotted range for their status (and tell me if they're over or under - I'd like to get rid of the "false" result when it's within the correct range - but like I said, I'm a novice.)

The initial imported data could come from a report that I'd run almost daily, and with all of the formatting I have to do first and then updating the formulas to point to the correct cells, I'm hoping to have a formula that I can easily re-use.

Any ideas on how to combine all of these steps (and eliminate any redundant or unnecessary steps) into one formula?

Thanks for your help! I continued to work on it too and came up with an alternate work around for the multiple last names after I realized that even though that two sheets were pulling the data from the same database (and thus the names should be output in the same way) it seemed that there were some cases where the first name was also showing a middle initial after it on one sheet but not on the other - argh! (i.e.: my original "Faculty Seniority List" sheet had a name column that would read "Thompson, Earl S." and my original "Load Hrs" sheet would have a last name column with "Thompson" and a first name column with "Earl" and the "S." was nowhere to be found....)

So on the original "Faculty Seniority List" sheet I inserted a few extra columns, and then ran a "text to columns" on the name so as to separate it out and delete the column with the middle initials. Then in another newly inserted column, I used a concatenate formula to re-combine the name without the middle initial. I then copied and pasted the values only into yet another newly inserted column. Then on my "Load Hrs." sheet I inserted a few extra columns and entered a concatenate formula again (copying and pasting the values into another new column) so that my vlookup formula would return the correct value despite there being multiple people with the same last name.

Example.xlsm


I also played around with the "Hours OK?" formula a bit in order to eliminate the min/max helper columns I had inserted so it reads like this:

Formula:

Please Login or Register  to view this content.

I was thinking the same thing about the named table references

All that being said - you're right - 6 months from now the inner workings would SURELY be forgotten, and with the data being imported on a frequent basis (via importing a text file saved from our Datatel UI database), I'm fine with what I've got and I'm currently working on writing a macro that will do all of this for me..... but that's a whole other thread!