Solved

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

Posted on 2015-01-07
11
305 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
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 will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

705 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

14 Experts available now in Live!

Get 1:1 Help Now