Essential to Expert Skills progression

Excel tutorials

The only Excel books you will ever need

  • The only constantly updated Excel 365 titles more…
  • Thoroughly covers Power Pivot and Power Query more…
  • Covers business intelligence and OLAP features more…
  • Simple, easy, understandable, 2-page lessons more…
  • Available as both printed books and e-Books more…
  • Used by schools, colleges and universities¬† more…
  • Available for 365, 2019, 2016 and Mac versions more…

Using VLOOKUP, if Column 1 is blank, get value from Column 2

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.

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Recent Articles

VLOOKUP inexact match illustration (chicken and egg).

VLOOKUP inexact match

VLOOKUP lesson with sample file that will teach you everything there is to know when creating a VLOOKUP inexact match Excel function.

VLOOKUP exact match

Simple VLOOKUP tutorial with sample file that will teach you everything there is to know when creating a VLOOKUP function with an exact match.

x-lookup-tutorial-part-one

Excel XLOOKUP function (part one)

The new Excel XLOOKUP function was introduced in the July 2020 Excel 365 semi-annual update.  It isn’t available in older versions (Excel 2019 and earlier). 

VLOOKUP using text strings

VLOOKUP using text strings

How to use VLOOKUP to search for a piece of text and return a matching value. This article also explains what VLOOKUP is and when to use it.

2 Responses

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

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

Leave a Reply

Your email address will not be published.

thirteen + 20 =