Solved

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

Posted on 2015-01-07
11
306 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
  • 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
 

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
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.

 
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 500 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:itjockey
ID: 40536659
Mr.BRIDGEDIVISION,

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

See attached

Thanks
EE-Sample.xlsx
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

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.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

932 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

11 Experts available now in Live!

Get 1:1 Help Now