Sifting through multiple matches in vlookup()?

[Freedster]

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

 

[Freedster]

OK, combining the names seemed to have worked, and I have the info I need. I
did a simple =C2&" "&D2 column on each sheet to append the names together and
then I was able to vlookup() against that for my departments, etc.

I'm still curious though if there's a way to do it without adding columns to
both sheets, though. No rush.

- Freed

 

[Spiky]

OK, combining the names seemed to have worked, and I have the info I need..  I
did a simple =C2&" "&D2 column on each sheet to append the names together and
then I was able to vlookup() against that for my departments, etc.

I'm still curious though if there's a way to do it without adding columnsto
both sheets, though.  No rush.  

Try putting that right in your VLOOKUP instead of in a column.

 

[Shane Devenshire]

Hi,

Try this

=INDEX(I1:I3,SUMPRODUCT(--(A1=G1:G3),--(B1=H1:H3),ROW(H1:H3)))

Where the First Name of the individual is in A1 and is listed in G1:G3, and
Last Name in B1 and somewhere in H1:H3, I1:I3 has the listing of the
departments.

Just one point - first and last names together are not a good primary key,
there could be duplicates of the combination.

 

[Freedster]

Yeah, I know it's not a good primary key. I personally know of several
people with approximately the same name in the company (Ron & Ronnie, or
James & Jamie). I also know that this isn't the case with any of the names
associated with the servers, though.

This is kind of a one time "maintenance and current status" spreadsheet. It
helped me find about 50 servers on the list that now need to be examined and
updated, and that was kind of the point. It's not a permanent recurring work
item, otherwise, I'd worry more about the potential for duplicate keys.

Thanks again for the tips!