Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to match the data from one table to another.

The primary table has two columns (an alpha item description in column A and a numeric value for that item in column B) in an unsorted order. The primary table must remain in this format, I can't sort them.

The secondary table of data I am trying to match also has two columns (an alpha item description in column A and a numeric item value in column B) in an unsorted order, is not sorted the same way and is missing some of the rows of the primary table.

I would like the primary table to search the secondary table for a matching item description and copy that items numerical value.

If the secondary table does not have that item I would like to leave that cell blank.

Is there an excel formula to resolve this issue. Since I am new to this formula I can't post an example. Thanks for the help.

share|improve this question
2  
VLOOKUP should do the trick; wrap it in an IFERROR function to show blank when no match, as in: IFERROR(VLOOKUP(<cell_reference_of_value_to_lookup>,<range_to_look_in>,<column_‌​with_values_to_return>,FALSE),""). The FALSE tells VLOOKUP to only make an exact match. –  chuff Jun 5 '13 at 17:11

2 Answers 2

Your question is a bit confusing. Maybe an example of your starting data would help.

However, this looks like a case where I'd use VLOOKUP. Here is the Microsoft Documentation on VLOOKUP.

Lets say Sheet 1 contains your primary table, and Sheet 2 contains your secondary table.

An example formula for cell B3 on Sheet 1 using VLOOKUP: =VLOOKUP($A3,Sheet2!A1:B210,2,FALSE)

The ID from Sheet 2 will show up in Column B.

By setting the fourth parameter to false, I've told VLOOKUP that the data is not sorted.

By default, or when the parameter is explicitly true, VLOOKUP assumes the data is sorted, and finds an approximate value. Setting it to false forces VLOOKUP to find an exact match.

However, if VLOOKUP can't find a match, it will show N/A. You want an empty cell, so you can wrap it in IFERROR. The example is changed to: =IFERROR(VLOOKUP($A3,Sheet2!A1:B210,2,FALSE),"")

share|improve this answer
    
Maybe this will help clarify my issue. Sheet1 contains two columns(A= item description, B is currently empty) with 231 rows that can't be changed in any way. Sheet2 contains two columns(A= item description, B is a numerical value) with 210 rows, it can be sorted if necessary and is in a different order than sheet1. I need sheet1 to search sheet2 and enter sheet2 numerical value for the matching item on sheet1. If no item is found on sheet2, sheet1 value should be blank. –  Todd A Jun 5 '13 at 17:49
    
OK, changed my answer to reflect your comment. –  daxlerod Jun 5 '13 at 21:39
    
I tried this formula but it did not seem to do what I needed it to. Is there a way to make this work with three pages being searched (sheet 2 to sheet4)and returning the value to sheet1 ? –  Todd A Jun 18 '13 at 15:33
    
Maybe something like =IFERROR(VLOOKUP($A3,Sheet2!A1:B210,2,FALSE),IFERROR(VLOOKUP($A3,Sheet3!A1:B210‌​,2,FALSE),IFERROR(VLOOKUP($A3,Sheet4!A1:B210,2,FALSE),""))) –  daxlerod Jun 18 '13 at 18:54

You can use Query from Excel Files :

  • Define name for primary table dataset (Formulas tab -> Define name)
  • Define name for secondary table dataset
  • Go to Data tab, select "From Other Sources", and from the dropdown, select "From Microsoft Query"
  • Select your workbook file and confirm that you want to merge the columns manually
  • In the following window "Query from Excel Files", drag&drop the column A of first dataset into the column A of second dataset - a link between these columns will be created
  • Go to File menu, click "Return Data to MS Office Excel", an Import Data dialog will pop up
  • Select the sheet into which you would like the matched data to be imported
  • Click OK - you should see matched data with columns from both tables

Or if you don't mind uploading your files to an online service, you can use for example http://www.exmerg.com/data-merging and match the tables using drag&drop (Disclaimer: I am author of the tool).

Hope this helps.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.