Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sifting through multiple matches in vlookup()?
This is an asset managment issue, in a way. I've got two sheets I'm trying
to aggregate to figure out what department owns what servers. On one sheet I've got servernames, and the first name and last name of the owners. For what it's worth, some servers have multiple owners. This has about 400 records. On the second sheet I've exported the entire company directory. I've got several fields, but I've parsed and normalized things out so I have the first and last names, the departments they work for, and who they report to. This has several thousand records and I can sort it however I like. My problem is that if I vlookup() by first name or last name for a given server, I'm inevitably going to get multiple people with that same first or last name that aren't the owners. In other words, if I vlookup() "Larry Jones" by his first or last name, I'll get 3 Larry's and 8 Jones's. What I really need to do is vlookup() the pair together; somehow vlookup() Larry among the Jones's (or vice versa), and cross reference his deparment number, etc. with Larry Jones's server. Caveats: - I can do whatever I want with this data to manipulate it as needed to make this easier. - There is a "full name" field in the company directory, but it's not normalized and in cases it may or may not have a middle initial. Simply combining "first name" and "last name" on the server sheet won't equate to the "full name" field on the directory sheet. - We can assume that there's no two people with the same first and last names. I'm going to try to combine the first names and last names on both sheets with a column of text() cells to see if that works, but I'd still be curious how something like this could be accomplished with just lookups and without adding data. Thanks in advance for any advice, - Freed -- - Freed Business Continuity Planner Excel Wizard-in-training |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP and multiple matches | Excel Discussion (Misc queries) | |||
Vlookup to return the sum of multiple matches | Excel Discussion (Misc queries) | |||
How do I get multiple matches when using the Vlookup function? | Excel Worksheet Functions | |||
Multiple matches on VLOOKUP | Excel Worksheet Functions | |||
how to deal with multiple matches on vlookup? | Excel Discussion (Misc queries) |