Expert: Bill Hermanson - 11/9/2009

Question
Hello Bill,

I am working with two excel sheets W1 amd W2. I have some charecters in W1 and corresponding values are to be imported from W2.

The problem is that the charecters are not the same in W1 and W2, but are similar. The values are in different order between the excel. Please suggets an easier way how i can compare the charecters between the excel and then how i can import the corresponding values in to the excel W1

Answer
Terrance,

  You will use the VLOOKUP function to compare the characters (hopefully you mean WORDS) between W1 and W2.  VLOOKUP can compare words, find a match from one table to the next, and import corresponding values from one sheet to the other.  It's designed to do just that.

   The order of the 'characters' [words] doesn't need to be the same... VLOOKUP can find them no matter what the order is.  BUT, they do need to be spelled the same!  WILLIAM and WILLAIM will NOT be seen as a match.  if you have mis-spelled or non-matching words, then the problem is MUCH more difficult, if not impossible.

  So read about VLOOKUP, and also enjoy the little tutorial I've included below. if you have additional Q's after you have looked this over and tried it out, please write a follow-up.

   >>> Bill

=================== VLOOKUP TUTORIAL ====================
  0) VLOOKUP is used to look up values from one table or list or database, into another cell or table or list.  Each value to be retrieved must have it's own VLOOKUP().  The idea is to search a column in your list or database for a 'lookup value'.  When that lookup value is found in the list or database, a value from another column, in the same row as the matching value, can be retrieved from the database.

The VLOOKUP syntax is:

  VLOOKUP(lookup-value,table-array, col-index-num,range-lookup)

  In more English terms, I would write:

  VLOOKUP(look-for, database, column-num, mode)

 1)  The lookup-value is what you're trying to find in your database.  Note that the column(s) in your database  which you are trying to return information from,  MUST be to the RIGHT of the column that contains the matching value(s).  VLOOKUP cannot lookup to the LEFT of the matching-values column.  (But there are tricks you can play to get around this, and other functions you can use instead, like MATCH %26 INDEX).  I call the column which you are searching for the lookup-value in, the 'matching values column'.

 2) The table array is the database from which you are trying to lookup values.  If you are going to use more than one VLOOKUP, the table-array MUST be specified as a double-absolute reference of the form $C$5:$Z$500.   If you bungle this, then successive copies of the VLOOKUP copied down a column, will each refer to a slightly different database! [They will differ by having one row removed from the top, and one extra row at the bottom, for each copy].  
    Even if the matching-values column is NOT the leftmost column in your database, the matching-values column MUST be the leftmost column that's specified by the table-array.  It is always 'column #1'.  You can specify part of your database as the table array; columns to the LEFT of the matching-values-column will not participate in the VLOOKUP function.

  3) The col-index-number is the number of columns to the right of the matching-value-column from which you want to retrieve information.  Just count columns, using the matching-value column as #1.  If your matching-values column is a list of NAMES, and the persons telephone number is three columns away, then the col-index-num for the telephone number is 3.  
     It is good practice to put this number in the top-most cell of the column containing the VLOOKUP, and refer to that cell with an mixed reference (like G$1).  With that type of reference, you can copy the VLOOKUP horizontally as well as vertically and it will continue to work, (with a different number at the top of each VLOOKUP column) and you can instantly pick which column to get the result from without re-editing the VLOOKUP to have a different col-index-num!!! Also, if the number needs to change (you counted wrong, or someone inserts a column into the database which makes everything move!), you can change the number in the top cell without having to rewrite and re-copy the entire column of VLOOKUPS.  It's really bad form to type the col-index-num directly into the VLOOKUP function.

  3) If you become an advanced user, you can replace the hard-coded col-index-number at the top of the VLOOKUP column with an EQUATION which CALCULATES the column number, so if someone DOES insert a column in the database, all your VLOOKUPs continue to work properly!

  4) The Range-Lookup value controls how VLOOKUP will work.  Usually the Range-Lookup value is a 0 or FALSE.  In my experience, 1 (or TRUE) is very rarely used.  A 1 for the Range-Lookup value  allows VLOOKUP to return an 'approximate' match, and its anybody's guess how 'approximate' is defined by Excel.  Sometimes, however, a 1 will serve your purpose so don't rule it out entirely.  if you use a 1, the database 'must' be sorted in alphabetical order of the matching-values column.  Beware: with a Range Lookup of 1, VLOOKUP can return a result that you would consider to be 'incorrect'.
     If you are just getting started with VLOOKUP, I recommend that you put the range-lookup value in a cell somewhere above your VLOOKUPs and refer to it with an absolute reference like $B$2.  That way, if you want to change it, or see the effects of the two different values (0 %26 1), you can just type a new value in the cell and all the VLOOKUP results will change instantly.  

========================= END OF TUTORIAL=========================