Forum Discussion
Excel Formula Help....
Not quite. I would like to simply enter a zip code and have the name of the recruiter populate. That's it.
I figured since you had the names of the schools you wanted to be able to give anybody calling from outside of your own zone of responsibility not only the name of the recruiter but also the several schools at which he or she might be found.
I have attempted to sort & filter and did not like the result due to the fact that it would skew the info of the other columns.
I'm not sure what you mean by "it would skew the info of the other columns"
IF there is only a one-to-one relationship between any given zip code and any given recruiter, a simple VLOOKUP would work with that same table.
Excel works far better with a single table as the source for information.
By way of background in Excel basics, it's often important in situations like this to separate the input (or data source) end of things from the output. Part of what made your original layout difficult to work with was that you'd (to this Excel basics way of thinking) confused the two, by putting time and energy into arranging multiple lists of schools and their associated zip codes under the names of each of the several recruiters, thus, in effect, needing whatever formula or function you might come up with have to look in multiple places for a single (and simple) piece of information. You also put time and energy into making it look nice. I have no issue with making things look nice, but would recommend that you begin with first making any workbook function; add the sizzle after it functions.
So here's a variation on what I set up last night, this time using VLOOKUP to find the entered zip code and deliver nothing but the name of the corresponding recruiter. And I just want to note that (because there are quite a few zip codes that appear more than once in your total database, this will just get the first instance of a match. That will serve your stated need IF there aren't any cases of two (or more) recruiters overlapping in a given zip code.
I have attempted to sort & filter and did not like the result due to the fact that it would skew the info of the other columns.
Not quite. I would like to simply enter a zip code and have the name of the recruiter populate. That's it.
I figured since you had the names of the schools you wanted to be able to give anybody calling from outside of your own zone of responsibility not only the name of the recruiter but also the several schools at which he or she might be found.
I have attempted to sort & filter and did not like the result due to the fact that it would skew the info of the other columns.
I'm not sure what you mean by "it would skew the info of the other columns"
IF there is only a one-to-one relationship between any given zip code and any given recruiter, a simple VLOOKUP would work with that same table.
Excel works far better with a single table as the source for information.
By way of background in Excel basics, it's often important in situations like this to separate the input (or data source) end of things from the output. Part of what made your original layout difficult to work with was that you'd (to this Excel basics way of thinking) confused the two, by putting time and energy into arranging multiple lists of schools and their associated zip codes under the names of each of the several recruiters, thus, in effect, needing whatever formula or function you might come up with have to look in multiple places for a single (and simple) piece of information. You also put time and energy into making it look nice. I have no issue with making things look nice, but would recommend that you begin with first making any workbook function; add the sizzle after it functions.
So here's a variation on what I set up last night, this time using VLOOKUP to find the entered zip code and deliver nothing but the name of the corresponding recruiter. And I just want to note that (because there are quite a few zip codes that appear more than once in your total database, this will just get the first instance of a match. That will serve your stated need IF there aren't any cases of two (or more) recruiters overlapping in a given zip code.
mathetes My apologies for the late response!
Yes, I left a few of the zip codes blank due to the fact that there were multiple schools with the same name in the area; so after I got my coworkers to verify the correct ones, I added them in. I noticed the "Zip Code not found" error message. I like that!I really do appreciate all your help!
Here's a quick enhancement: I created a separate table with the name of the recruiter, telephones and email addresses. You'd have to put the real ones in, but this shows how easily a simple table can be added, and a second set of lookups that use the result of the first (name of recruiter, in this case) to retrieve that person's telephone or email.
By the way, both VLOOKUP functions now are looking into a named range to retrieve the requested information. If you add rows, which is entirely possible, you may need to extend those ranges.
Wow! This is much better than anything I would have been able to come up with!
I've been at this kind of thing for at least 40 years now (since the mid-70s), so I should have learned a few things in that time. But thank you. The VLOOKUP function is, from what I've read, the most common function after the basics of SUM and the like. And it is a very useful function to become familiar with. As I just said, there are other ways to do the same thing--within Excel, users often comment on how many different ways there are to "skin the same cat."
In this very slightly revised version of that, I've put the Lookup (the user-oriented page) on a sheet of its own, in effect hiding the database on a second sheet. You could even hide that altogether, although that isn't really necessary in your small fraternity of users. Just showing you what can be done.
You are aware, I trust, that there are several rows in the database where there's no zip code--I don't know if you just hadn't found it yet, or you were thinking it was the same as the row above. This functions with those blanks, but if in fact the missing zip codes are unique, this whole little application you've created would be incrementally more complete if you could add those in.
I also was thinking that (unless there's a privacy issue) you could enhance this further by adding the phone numbers of each recruiter, and/or an email address. That would involve adding column(s) to the database. Or, if we wanted to get fancy, another side database connecting each name with that contact info; this would be more elegant. Let me know if that would interest you.
I also appreciate that you were also able to list the recruiters and schools as the column instead of the row. You were right, it functions much better this way!
VLOOKUP, FILTER, INDEX and MATCH....that whole set of data retrieval functions...all work with a single database. There is also Power Query now, with some of the newest editions of Excel, and with that you can combine multiple data sources. Useful in advanced applications, but far more power than you need here.
Thank you so much!
You're very welcome. I hope your recruiting efforts are helped by all of this.
- Wow! This is much better than anything I would have been able to come up with! I also appreciate that you were also able to list the recruiters and schools as the column instead of the row. You were right, it functions much better this way!
Thank you so much!