While I am no Excel expert, I do have to use this application a lot for my job. My wife laughs when I suggest using technology to resolve her problems in her job as a journalist and insists on doing things longhand. Anyway, I’ll keep trying. Most of my time is spent migrating various mail platforms to Exchange or BPOS so I’m constantly building migration databases to stay organized.
When I usually have several different data sources to deal with I use Access to tie them all together. This has worked great for me and I still use it. Although I knew about the various lookups in Excel, I never thought they were all that helpful or powerful until I needed a quick and somewhat simple way for me to lookup two possible matches for a given field.
So my scenario starts like this. My migration application would update the user’s email address to the new migrated one within its database. There was mailbox size data associated to this email address. In my migration database (Excel spreadsheet) I had some lookup columns that were pretty straightforward but I realized I had a snag when the migration application changed this key field. Suddenly the logic in my lookup made no sense and returned zero matches for some users. Once I identified what was happening I needed a simple way to lookups that might contain one of two possible values, like two different email addresses.
Trying to Google my scenario was difficult. I couldn’t think of the right keywords to search for. After some poking around with Excel’s online help and Googling some resources I ended up just using Excel’s built-in help. It gave me a starting point and I went through every logical and lookup and reference until I was able to piece together what I needed. Needless to say it took me several trials before I got the results I wanted.
My workbook looked like this. This example is significantly trimmed down to make it more clear. I had the main view of my database with all the names and fields I wanted. I also had data from my migration application which contained the mailbox size information. These can be seen on the two tabs in my workbook.
Here’s the data from the Migration Database sheet. We have the account information and the old and new email addresses. The last three columns contain the lookup formula that pulls data from the Mailbox Stats sheet.
Here’s the data from the Mailbox Stats sheet. Notice the mixture of addresses and even one that doesn’t match up with either the old or new address for ‘taccount5’.
And now on to the formula. As I said earlier it took me several tries to get the formula to return the results I wanted. Here’s the formula:
=IFERROR(IF(ISERROR(VLOOKUP(‘Migration Database’!C2,’Mailbox Stats’!A:D,2,FALSE)),VLOOKUP(‘Migration Database’!D2,’Mailbox Stats’!A:D,2,FALSE),VLOOKUP(‘Migration Database’!C2,’Mailbox Stats’!A:D,2,FALSE)),"")
Pretty confusing, huh? Well, looking at a long formula with lots of commas, parentheses, exclamation points, etc. is very confusing at times. Luckily Excel makes this easier by highlighting the sections within the formula that go together.
Let’s breakdown the formula. First, I had to determine the root of what I was looking for. I wanted to match up one of two possible values for the email address and then perform a lookup on the Mailbox Stats sheet and return the result. Also, if there were any errors in matching up the values, I wanted to return blank values instead of the errors like these (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!). At first I thought an OR logical function is what I needed but I chose a simple IF function instead. I’m sure there are a few different ways to achieve the same result however but this was simple and worked for me. So the first part of the IF function is to perform the logical test, in other words, if something returns a true or false. I did this by using the ISERROR function and tested whether or not looking up my new email address triggered a failure or error as described above.
Let’s step back a minute and look at our IF function. First, there’s the test, then depending on the result (TRUE or FALSE) you can perform different calculations. You can nest these and other functions to provide more granular information. For me this is like trying to code a complex script on one line. It’s easier if you can break up the logic into smaller chunks.
IF(logical_test, value_if_true, [value_if_false])
This next part of the formula performs the test. We look up the value in C2 on the Migration Database sheet in the Mailbox Stats sheet and if we have a match it returns a FALSE value. Conversely, we return TRUE if there is no match.
ISERROR(VLOOKUP(‘Migration Database’!C2,’Mailbox Stats’!A:D,2,FALSE))
Once we have our test result we perform either the value_if_true or value_if_false part. In my example, I look up the new email address first and if that returns an error, I look up the old email address and return the values from the Mailbox Stats sheet I’m looking for. So in looking at the entire IF function this satisfies our goal.
IF(ISERROR(VLOOKUP(‘Migration Database’!C2,’Mailbox Stats’!A:D,2,FALSE)),VLOOKUP(‘Migration Database’!D2,’Mailbox Stats’!A:D,2,FALSE),VLOOKUP(‘Migration Database’!C2,’Mailbox Stats’!A:D,2,FALSE))
The last piece of the puzzle has to do with cleaning up the data in the event of a total failure in our lookup, meaning neither the old or new email address are found. That’s where the IFERROR function comes into play.
IFERROR(value,value_if_error)
So basically with this wrapped around the IF function we are saying that if we receive an error to return some value. This could be another calculation or simple text that’s nicer than one of these (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!). In my formula, I just wanted to return a blank so hence the double quotes (“”) at the end.
I hope this article saves someone some time looking for the same logic or spurs further investigation into some of these powerful Excel functions.
.
