Excel – Multi-column VLookup

When analyzing data in Excel at times it is necessary to pull data from another data source based on more than one column. In this case you can use the MATCH function in an array formula. Just so you understand the general idea of an array, it is a collection of values. The match function is generally used to find a value in an array. Knowing this, we will use this Excel file to show how to engineer a multi-column VLOOKUP in a few simple steps.

Source Data Tab

Above is a screenshot of the data in the Source Data tab. On our Lookups tab I started off with just the first names and last names of some users. In order to pull the corresponding usernames and DOBs (dates of birth) I did the following:

  1. Setup a column indicating where the matching data was found by using the following array formula:
    {=MATCH(1,('Source Data'!$A$2:$A$27=$A1)*('Source Data'!$B$2:$B$27=$B1),0)}

    • It is important to note that even though the formula is surrounded by curly braces, those are put there by Excel (NOT MANUALLY) when executing the formula by using the CTRL+SHIFT+ENTER key combination.
    • The first parameter (1) indicates the value being searched for in the second parameter.
    • The second parameter is an array which remains such only because of the CTRL+SHIFT+ENTER key combination used to finalize the formula.

      • That will produce an array of 0s and 1s which are coerced into the numbers because of the multiplication operation.
      • If there was only one column checked and the multiplication were taken out, the array will be full of TRUEs and FALSEs. For example the formula=TRUE*TRUE results in 1 while TRUE*FALSE results in 0.
    • The third parameter 0 indicates that the first parameter must match be an exact match of one of the items in the array (second parameter).
    • The value returned will be the position (start at 1) of the value in the array if found. If not found an N/A error will be returned.
  2. Setup a new column which references the matching row column to pull the corresponding username if a matching row was found. If the row wasn’t found show the cell as empty:
    =IF(ISNA($C2),"",INDEX('Source Data'!$C$2:$C$27,$C2))
  3. Setup a new column which references the matching row column to pull the corresponding DOB if a matching row was found. Format the date using the YYYY-MM-DD format. If the row wasn’t found show the cell as empty:
    =IF(ISNA($C2),"",TEXT(INDEX('Source Data'!$D$2:$D$27,$C2),"YYYY-MM-DD"))

After doing the above three steps in row 2 of the Lookups sheet and copying the formulas down, the final result was this:

Lookups Tab

If I were to clean this up a bit more I might hide column C of the Lookups tab, but other than that there really isn’t much else you need to do. I know this may still seem a bit mysterious, but with practice you will begin to understand array formulas better and perhaps write other useful array formulas to get the job done. For all of you who came to figure out how to do a multi-criteria lookup, I hoped it helped but if not, comment on what needs clarifying and I will do my best to help. 😎

JavaScript Snippet – Convert Number To Column Name

Following yesterday’s post, I thought it appropriate to write a quick JavaScript function which converts an integer into the corresponding column name:

/**
 * Takes a positive integer and returns the corresponding column name.
 * @param {number} num  The positive integer to convert to a column name.
 * @return {string}  The column name.
 */
function toColumnName(num) {
  for (var ret = '', a = 1, b = 26; (num -= a) >= 0; a = b, b *= 26) {
    ret = String.fromCharCode(parseInt((num % b) / a) + 65) + ret;
  }
  return ret;
}

You can click here to enter a positive integer and see the corresponding column name. If you need this function for a project, feel free to use it as long as you give credit. 8)

JavaScript Snippet – Parse A Spreadsheet Address

Today I am starting work on a quick flashcard app that will use Google Docs to read data from a spreadsheet. I thought I had written and published a function which parses an Excel like cell address and converts it into the column number and the row number but I wasn’t able to find it. Thusly I wrote the following parseAddress function:

/**
 * Takes in a cell address and returns an array containing the corresponding
 * column number followed by the row number.
 * @param {string} strCellAddr  The address of the cell (eg. AD32).
 * @return {!Array.<number>}  An array of two numbers:  the column number and
 *     the row number.
 */
function parseAddress(strCellAddr) {
  var i = strCellAddr.search(/\d/);
  var colNum = 0;
  strCellAddr = +strCellAddr.replace(/\D/g, function(letter) {
    colNum += (parseInt(letter, 36) - 9) * Math.pow(26, --i);
    return '';
  });
  return [colNum, strCellAddr];
}

This function takes the cell address as a string (eg. AD32) and converts it to an array (eg. [30, 32]). The first value in the array will be the column number and the second will be the row number.