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!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

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!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,017
Messages
5,834,960
Members
430,330
Latest member
drAli77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top