Results 1 to 8 of 8

Creating IF/THEN VLOOKUP statements?

This is a discussion on Creating IF/THEN VLOOKUP statements? within the Excel Questions forums, part of the Question Forums category; I have a master worksheet which is populated by data from other worksheets. I use vlookup to do this. This ...

  1. #1
    New Member
    Join Date
    Dec 2002
    Location
    Southern California
    Posts
    8

    Default Creating IF/THEN VLOOKUP statements?

    I have a master worksheet which is populated by data from other worksheets. I use vlookup to do this.

    This is sort of a total sales spreadsheet. The Master worksheets will show how will a product has sold in certain markets. Each of the sub-worksheets will represent data in these markets.

    So I'm using VLOOKUP to scan each data worksheet, and if there is a header that matches a header on the master worksheet, then it's supposed to take the dollar figure next to it and plop it into the master worksheet.

    The equation looks like this:

    =VLOOKUP($C6,'test'!$B:G,3,FALSE)

    This works just fine. It looks on worksheet 'test', find the row which matches the text in cell C6 in the master sheet, then pulls the dollar figure next to it.

    However, sometimes there will be no data elements in the worksheet that match the text in cell C6.

    For example, say cell C6 on the master worksheet says ' BOB,' . If worksheet 'test' has a cell that says 'BOB' and we sold $10,000 worth of untis in 'BOB,' then the master spreadsheet will reflect that.

    However, if the worksheet 'test' does not have any cells that say BOB, I get a #N/A error in the master spreadsheet. This may hapen because while we track sales for BOB, there may not have been any sales for BOB this quarter.

    I was trying to work up an IF statement so that if there is no value, it would just put in the number 0.

    =IF(VLOOKUP($C4,'test'!$B:G,3,FALSE)<>"",VLOOKUP($C4,'28days'!$B:G,3,FALSE),0)

    But this doesn't work either.

    Am I making any sense? Does anyone have any ideas on how to resolve this?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,196

    Default Re: Creating IF/THEN VLOOKUP statements?

    Either

    =IF(ISNA(SETV(VLOOKUP($C6,'test'!$B:$G,3,0))),0,GETV())

    which requires the morefunc.xll add-in

    or

    =IF(ISNA(V(VLOOKUP($C6,'test'!$B:$G,3,0))),0,V())

    which requires the following code in your workbook as a module:

    Public Function V(Optional vrnt As Variant) As Variant
    '
    ' Stephen Dunn
    ' 2002-09-12
    '
    Static vrntV As Variant
    If Not IsMissing(vrnt) Then vrntV = vrnt
    V = vrntV
    End Function

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Brampton
    Posts
    325

    Default Re: Creating IF/THEN VLOOKUP statements?

    Why not use simply:

    =IF(ISERROR(VLOOKUP($C6,'test'!$B:G,3,FALSE)),0,VLOOKUP($C6,'test'!$B:G,3,FALSE))

    It works for me. Am I missing something?

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454

    Default Re: Creating IF/THEN VLOOKUP statements?

    Hi Corni:

    Aladin's proposed solutions alleviate the need to do the VLOOKUP computation twice. A simple analogy woukd be ... why pay for something twice, if it has already been paid for once (provided we have kept the receipt for it.)
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    New Member
    Join Date
    Sep 2011
    Posts
    1

    Talking Re: Creating IF/THEN VLOOKUP statements?

    Quote Originally Posted by Corni View Post
    Why not use simply:

    =IF(ISERROR(VLOOKUP($C6,'test'!$B:G,3,FALSE)),0,VLOOKUP($C6,'test'!$B:G,3,FALSE))

    It works for me. Am I missing something?
    This is perfect Corni!!
    I was missing the ISERROR function.
    Thank you so much!

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,196

    Default Re: Creating IF/THEN VLOOKUP statements?

    Quote Originally Posted by Berine View Post
    This is perfect Corni!!
    I was missing the ISERROR function.
    Thank you so much!
    If you are on Excel 2007 or later, you can also invoke...

    =IFERROR(VLOOKUP($C6,'test'!$B:G,3,0),0)
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    Apr 2013
    Posts
    5

    Default Re: Creating IF/THEN VLOOKUP statements?

    Quote Originally Posted by Aladin Akyurek View Post
    If you are on Excel 2007 or later, you can also invoke...

    =IFERROR(VLOOKUP($C6,'test'!$B:G,3,0),0)
    Thanks for sharing this, Aladin! This is exactly what I needed!

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,196

    Default Re: Creating IF/THEN VLOOKUP statements?

    Quote Originally Posted by Tulsi17 View Post
    Thanks for sharing this, Aladin! This is exactly what I needed!
    You are welcome.
    Assuming too much and qualifying too much are two faces of the same problem.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com