Solved

How to compare two columns in Excel and if match, then copy it

Posted on 2014-03-06
4
416 Views
Last Modified: 2014-03-06
I urgently need a formula for the following problem below. Tried a few suggested solutions online but cannot make it work.

Two Sheets

Sheet1
A | B
Name | Value
Mike | 10
Bob | 3
Chris | 7
John | 3


Sheet2
Name | Email | VALUE
Chris | chris@test.com |
Mike | mike@test.com |


Ok, what I want to achieve is to get the value from sheet 1 into Value in sheet2 of course the name has to match so Chris gets a value of 7, Mike - 10 etc.

I know it requires vlookup and iferror but my excel skills are not good enough to make it work quickly.

Thanks
0
Comment
Question by:m1979
4 Comments
 
LVL 19

Accepted Solution

by:
helpfinder earned 250 total points
ID: 39909593
use =VLOOKUP(A2;Sheet1!$A$1:$B$5;2;FALSE)
check my sample how it looks like in Excel

if you want to include als IFFERROR you can, I added 2 another rows in sheet2 (Kevin and Rosie) to demonstrate. For Kevin there is the same formula (VLOOKUP without IFERROR included) and because Kevin is not in the list in Sheet 1 excel gives back N/A (not available).
If you do not like to see N/A yoi can include IFERROR and tell Excel what do you like instead of N/A - in my example (Rosie row) I put text NOT IN LISt, but you can type what ever you want (text has to be quoted in the formula, so between ""; if you want to have just blank cell, so no N/A or custom text then use just "") - this is in sample1

PS: you may need to replace my semicolons (;) with commas (,) in the formula (it depends on your Regional settings)
sample.xlsx
sample1.xlsx
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 250 total points
ID: 39909619
Put this formula in C2 of Sheet2 and drag down till end of data. For sure if your data is more than the 4 rows that you mentioned you should change the Range to fit the data.

=VLOOKUP(A2,Sheet1!$A$2:$B$13,2,FALSE)

gowflow
vlookup.xlsx
0
 

Author Closing Comment

by:m1979
ID: 39909759
Thx guys, that did the trick. You saved my day
0
 
LVL 8

Expert Comment

by:itjockey
ID: 39909855
Ok try this, it is not required for this issue but in future you may

=IFERROR(VLOOKUP(A2,Sheet1!$A$2:$F$13,MATCH(C1,Sheet1!$A$1:$F$1,0),FALSE),"")

What is addition in formula from above?
A.IFERROR argument which returns to blank I.e. "" when there is error.
B.MATCH function which returns to column number from range, so in future if you add up some more  columns and what to lookup these values then you just have to change header on sheer 2. No need to rewrite whole formula.

Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now