Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2015-01-07
11
Medium Priority
?
352 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

610 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