• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

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

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
BRIDGEDIVISION
Asked:
BRIDGEDIVISION
  • 4
  • 4
  • 2
  • +1
1 Solution
 
ThomasMcA2Commented:
The VLOOKUP function will do that for you. See this question for a good explanation of what the VLOOKUP parameters mean.
0
 
BRIDGEDIVISIONAuthor Commented:
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
 
Tony PittCommented:
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!

 
BRIDGEDIVISIONAuthor Commented:
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
 
BRIDGEDIVISIONAuthor Commented:
looks like I didn't attach :)
Attached now.
EE-Sample.xlsx
0
 
Tony PittCommented:
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
 
Tony PittCommented:
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
 
ThomasMcA2Commented:
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
 
Tony PittCommented:
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
 
BRIDGEDIVISIONAuthor Commented:
Thanks, I'm still not sure what I was doing wrong but I'm testing and re-testing to find my mistake.
0
 
Naresh PatelTraderCommented:
Mr.BRIDGEDIVISION,

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

See attached

Thanks
EE-Sample.xlsx
0

Featured Post

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!

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now