?
Solved

Excel 2010; how to look up matching values on another sheet

Posted on 2015-01-07
11
Medium Priority
?
340 Views
Last Modified: 2015-01-07
I have two sheets in my workbook.  I need to find a way to match the contents of one column to the contents of another column in the second sheet.  To say it another way, I need to know if a given value in a cell in Column A is found in a column on another worksheet in the same workbook.
For example:
If Column A in Sheet A has 123456, I want to see if Column A in Sheet B also has 123456.
If it does, I'd like to take the contents in column B of the same row (from Sheet B) and copy them to column B in Sheet A (same row). If there is no match, then I'd like Sheet B, column B to say no match for that same row.

My preference is for someone to tell me how to do this using native Excel commands so I can learn from that.  If it makes more sense using scripting to accomplish this then I would need specific instructions on how to install/run and where to modify the script to match the columns and sheet names.
I'm using Excel 2010 and my experience with Excel is low but I have done some very minor work with scripts.

If it's necessary I can email the spreadsheet to you but I don't want to post it publicly.

I've attached a handwritten example but it's upside down :)
example.pdf
0
Comment
Question by:BRIDGEDIVISION
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 40536336
The VLOOKUP function will do that for you. See this question for a good explanation of what the VLOOKUP parameters mean.
0
 

Author Comment

by:BRIDGEDIVISION
ID: 40536374
I've actually been working with the vlookup and after several hours and renditions later I'm not making any progress.  I'll continue to look at the example you've sent but so far I'm still not doing something right.
0
 
LVL 4

Expert Comment

by:Tony Pitt
ID: 40536382
Is the issue that nothing appears, or that the wrong value appears?  It could be caused by the fact that the values are not in order, or that you're not specifying exact match, or probably lots of other things ...

Would you care to share the spreadsheet so that we can see it?  It would make it a great deal easy to find the problem.

/T
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:BRIDGEDIVISION
ID: 40536408
I've attached the spreadsheet.
Sheet 1, column B should contain Sheet 2 Column A if there is a match between the column C on sheet 1 and Column C on sheet 2.
0
 

Author Comment

by:BRIDGEDIVISION
ID: 40536410
looks like I didn't attach :)
Attached now.
EE-Sample.xlsx
0
 
LVL 4

Expert Comment

by:Tony Pitt
ID: 40536454
Right, so the first issue is that VLOOKUP can only use values further right than the match value.  It's therefore necessary to put the Device name first ...  If this is a one off, then simply drag Columns A and B to the right of Column C, and then delete the two empty columns.

There's something weird going on, however, because I can't get Formula to be recognised at all ...

/T
0
 
LVL 4

Expert Comment

by:Tony Pitt
ID: 40536486
I've no idea what was going on there.  I ended up having to export all the data from your spreadsheet as two .csv files and import them into a new file.  It then works!

Attached is the solution...

/T
EE-Sample-Fixed.xlsx
0
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 40536493
Your description mentions using Column A for the lookups, but it looks like you need to use Column C for the lookups. Put Column C first like Tony mentioned (in both sheets), then try this:

The following formula retrieves column B from Sheet2 into Sheet1:
=VLOOKUP(A2, Sheet2!A2:B309, 2, false)

Open in new window

But that doesn't display "No Match" for unmatched rows. To do that, check if VLOOKUP returns an error, like this:
=If(ISNONTEXT(VLOOKUP(A2, Sheet2!A2:B309, 2, false)),"No Match", VLOOKUP(A2, Sheet2!A2:B309, 2, false))

Open in new window

0
 
LVL 4

Accepted Solution

by:
Tony Pitt earned 2000 total points
ID: 40536506
Ah, the cells were all set to Text format.  They needed to be set to General, and then it would all have worked without moving the data to a new spreadsheet.  (See http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23366109.html for a little more discussion of this.)

/T
0
 

Author Closing Comment

by:BRIDGEDIVISION
ID: 40536523
Thanks, I'm still not sure what I was doing wrong but I'm testing and re-testing to find my mistake.
0
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 40536659
Mr.BRIDGEDIVISION,

why don't you use combination of INDEX MATCH function.

See attached

Thanks
EE-Sample.xlsx
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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