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
  • 887 posts

Posted 08 May 2015 - 11:51 AM

This article has moved

 

We have moved this article to our new site. You can see the answer to this question here:

 

https://thesmartmethod.com/using-vlookup-if-column-1-is-blank-get-value-from-column-2/


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