Jump to content


Photo

Using VLOOKUP, if Column 5 is blank, get value from Column 6

VLOOKUP IF tables structured references IFERROR

  • Please log in to reply
2 replies to this topic

#1 Simon E.

Simon E.

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 08 May 2015 - 10:05 AM

Hello

 

My query is really in the topic title.

 

I am in the midst of running my own exercises on various workbook uses and am currently building some basic invoices. I've come across what should be a very simple problem to solve but just can't get my head around it:

 

This workbook has several w/sheets, i.e, Invoice, Products and Customer List.  Using VLOOKUP, as per the lessons in Expert Skills I have easily referenced my products, products descriptions and prices in the invoice body.

 

When it comes to my customer address field in the invoice body, I have also successfully referenced my customer list w/sheet to that, using VLOOKUP, except for when it comes to a blank cell in the customer list w/sheet table.  My Customer List table has 2 fields (i.e. Address 1 and Address 2); Address 1 is always filled, but Address 2 is sometimes not (some addresses have several lines hence using 2 fields for the address).

 

Following Mike's lessons on this I understand the concept of using IFERROR with VLOOKUP, to return a value other an error, but what I would like to achieve is where the cell is blank (i.e. Address 1 field), move on to the next column (Address 2), and take the value from that instead.

 

Is this achievable using the VLOOKUP function or combined with another function(s)?

 

Hopefully the above is clear, but I can attach the w/book if required.

 

Thanks

Simon



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 884 posts

Posted 08 May 2015 - 11:51 AM

Hi Simon,

 

I think you can achieve this using an IF function rather than IFERROR.

 

You will need a formula that looks something like this:

=IF(VLOOKUP([@CustomerID],Customer,3)="",VLOOKUP([@CustomerID],Customer,4),VLOOKUP([@CustomerID],Customer,3))

 

This first checks whether your VLOOKUP for column number 3 returns a blank, then returns column number 4 if it does and returns column number 3 otherwise.

 

For a video walkthrough of the IF function, see: Expert Skills Lesson 3-5 Use the IF logic function.

For a video walkthrough of VLOOKUP, see: Expert Skills Lesson 3-22 Use a VLOOKUP function for an exact lookup.

 

You'll also notice that my formulas are using structured table references like [@CustomerID]. You can use cell references in place of these, but structured table references do help to make formulas easier to understand and maintain.

 

You can see an introduction to tables and structured table references in the following video lessons:

Expert Skills Lesson 1-11 Convert a range into a table and add a total row

Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference

 

I have attached an example workbook showing this formula in action:

 

Attached File  VLOOKUPIF.xlsx   10.08KB   924 downloads

VLOOKUPIF.png

 

I hope this is what you were looking for, but please feel free to reply if you need any more assistance.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Simon E.

Simon E.

    Newbie

  • Members
  • Pip
  • 5 posts

Posted 08 May 2015 - 04:36 PM

Hi Jonathan

 

Many thanks with this, worked a treat and did exactly what I wanted to do.  Even better is that I understand why it works.

 

It had a knock-on effect on the address field in the main invoice body by leaving blanks or double-entries, but using a similar formula I was able to adjust those as well (took me a couple of hours to work it out though).

Thanks again

 

Simon.

 

As an aside, I have taken quite a few  Excel 2013 tutorials online (both paid and unpaid), before taking the Excel Central courses and I have to say, yours is by far the best.  Wish I hadn't bothered with the others...







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users