## 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.

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 `TRUE`s and `FALSE`s. 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:

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. ðŸ˜Ž

## Python – Convert Excel Column Name To Number

A few days I wrote a post about how to convert an cell address into coordinates (column number, and row number). I am working a quick Python script for work to import CSV data into a DB. In order to allow the user the ability to enter either the column number or the column name I used the following function to convert column names into number:

```def colNameToNum(name):
pow = 1
colNum = 0
for letter in name[::-1]:
colNum += (int(letter, 36) -9) * pow
pow *= 26
return colNum
```

As you can see, all you have to do is pass it the name of the column (letters) and the correspond column number will be returned. Have fun! ðŸ˜Ž

## 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)