One of our customers presented us with this problem. He had an Excel workbook containing address data, with the usual Address 1 and Address 2 fields. He wanted to use VLOOKUP to extract the first line of the address, but he noticed that in some cases the Address 1 field had been left blank and the first line of the address had been written in the Address 2 field instead.
Here’s a mockup example that I created:
You want to use VLOOKUP to extract the first line of the address into column H, but you want your formula to return the address from column D if the value in column C is blank.
Solving the problem with the IF function
The IF function allows you to solve this problem, though IF functions can be difficult to understand.
The IF and VLOOKUP functions are both fully explained in our Expert Skills Books and E-books.
The formula to solve this problem is:
=IF(VLOOKUP([@CustomerID],Customer,3)=””,
VLOOKUP([@CustomerID],Customer,4),
VLOOKUP([@CustomerID],Customer,3))
This formula works as follows:
IF the value in the 3rd column of the Customer table is blank, return the value in the 4th column of the Customer table, otherwise return the 3rd column of the Customer table.
Sample Workbook
You can download a copy of the sample workbook to see the formulas in action.
These are the only up-to-date Excel books currently published and includes the new Dynamic Arrays features.
They are also the only books that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.
Some of the things you will learn
2 Responses
Look great. You are the first that has posted what I am looking for. Unfortunately it is not working yet. My values need to come from another table. Is that possible? I’m not seem to get the formulas right.
Hi Diana
I cover the VLOOKUP, HLOOKUP, INDEX and MATCH functions extensively in Session 3 of the Expert Skills book along with the new IFS and SWITCH functions (added to Excel in February 2016) that can be more useful than VLOOKUP in some circumstances. If you complete Session 3 and are still unable to do exactly what you need let me know the precise problem you wish to solve and I’ll point you to the lesson in the book that provides the best solution.