Q: VLOOKUP vs INDEX/MATCH

cong05072012

New Member
Joined
Sep 24, 2014
Messages
4
Good day. I am in the process of compiling 2 worksheets. Both have common information (first and last names) and I need the ID number and supervisor name from the other.

I copy-pasted the information to the same sheet. I have < ID #, last.name, first.name, supervisor > columns (N74:Q186 to be exact) and I want to move it to the sheet where it has < ID #, first.name, last.name, supervisor, location >.

First, I tried vlookup using the last name, but it pulls another employees information. Here's what I wrote:
=VLOOKUP(E74,$N$74:$Q$186,4)

Next I tried index/match based on the Mr.Excel article, but I just get an error. Here's what I wrote:
=INDEX($Q$74:$Q$186,MATCH(E74,$N$74:$Q$186,FALSE),1)

I am trying to match the employee's last name (E) to get the supervisor's name (Q). I have also used false and 0 but I still get the same error.



Thanks in advance!
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Mastaraz

New Member
Joined
Sep 24, 2014
Messages
29
You have duplicate names? Isn't it a better idea to use ID which will be unique?
 

Redsatan

New Member
Joined
Sep 24, 2014
Messages
7
Why use vlookup for the last name? Wouldn't you want to use vlookup on the ID like

=vlookup(ID,array_of_cells_here,2,FALSE) <-- placed in the cell where the first name is

The first ID in that formula checks what you put in the next table or cell where it says ID at. this is assuming you want the name to appear when you type in the id number. The array of cells will be N74:Q186 presumably. 2 is what you put to search for what is being put for the first name (assuming it's the second column in the row). You can just lazily copy and paste the rest changing the number 2 to whatever else you want the information to be.
 

cong05072012

New Member
Joined
Sep 24, 2014
Messages
4
The employee names are unique enough that there possibly only 3-5 duplicate values for every 100 names.

I cannot use the ID because one sheet has ID numbers while the other does not.


EDIT: Let me clarify. What I wanted to do is to retrieve values from list B and put it on list A. That's why I thought, index/match would have worked.
 
Last edited:

Vindaloo

New Member
Joined
Jul 15, 2014
Messages
29

ADVERTISEMENT

Your Match statement needs to be on one column. If you are looking up last name, that is the column you should be searching. Assuming Last Name is in column N; Row 1 is your column header; all data you want is between A1:Q186; B1 = First Name. Drag formula to the right and it will look up the next field. No changes Necessary. If C$1 = Supervisor, the formula will work.

=INDEX($A$1:$Q$186,MATCH($E74,$N$74:$N$186,0),MATCH(B$1,$A$1:$Q$1,0))
 

cong05072012

New Member
Joined
Sep 24, 2014
Messages
4
Your Match statement needs to be on one column. If you are looking up last name, that is the column you should be searching. Assuming Last Name is in column N; Row 1 is your column header; all data you want is between A1:Q186; B1 = First Name. Drag formula to the right and it will look up the next field. No changes Necessary. If C$1 = Supervisor, the formula will work.

=INDEX($A$1:$Q$186,MATCH($E74,$N$74:$N$186,0),MATCH(B$1,$A$1:$Q$1,0))
Thanks Vindaloo.

So you're saying I should put all the data together in one sheet (spanning from A to Q), correct? Following that suggestion, I removed unnecessary columns to make things easier and I have A to F:
first.name1, last.name1, ID #, last.name2, first.name2, supervisor (Name1 is from one file and name2 is from the other.)


Following your example still, is B$1 name1 or name2?

Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,203
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top