+ Reply to Thread
Results 1 to 6 of 6

IF function with INDIRECT VLOOKUP?

  1. #1
    Registered User
    Join Date
    02-07-2011
    Location
    England
    MS-Off Ver
    Excel 2013
    Posts
    42

    Question IF function with INDIRECT VLOOKUP?

    Hi Everyone,

    I hope someone can help me with what I think should be a very simple problem:

    I’m trying to use the IF function in addition to VLOOKUP using the formula below:

    =IF('Sheet1!$D2="","",(VLOOKUP(‘Sheet1!$D2,LIST,5,FALSE)))

    I have used this formula before and it has worked, the only difference now is that the reference cell is in a different tab to the formula.

    Should I be using some sort of INDIRECT or OFFSET function with this?

    Thanks for any help you can provide.

    E

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: IF function with INDIRECT VLOOKUP?

    The formula you posted should work fine so I'm not sure what your issue actually is. Could you post a sample workbook?

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: IF function with INDIRECT VLOOKUP?

    You have a single apostrophe before each sheet name - if the sheet name is exactly as shown you don't need those - try this

    =IF(Sheet1!$D2="","",VLOOKUP(Sheet1!$D2,LIST,5,FALSE))
    Audere est facere

  4. #4
    Registered User
    Join Date
    02-07-2011
    Location
    England
    MS-Off Ver
    Excel 2013
    Posts
    42

    Re: IF function with INDIRECT VLOOKUP?

    Hi,

    I've tried making a simpl version of my spreadsheet to attached and to my surprise, my origional forumla worked!

    So I'm stumped why it does not in my full version sheet.

    The Error it comes up with is: #NAME?

    Any ideas where I might be going wrong or what might be causing the conflict?

    Thanks
    E

  5. #5
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: IF function with INDIRECT VLOOKUP?

    Check that the reference of your defined name List is valid.

    Dom

  6. #6
    Registered User
    Join Date
    02-07-2011
    Location
    England
    MS-Off Ver
    Excel 2013
    Posts
    42

    Re: IF function with INDIRECT VLOOKUP?

    Thanks for your help everyone!!

    The answer was I was being stupid!! My list reference was not to the first colomn within that list but the 3rd, confusing excel and me!!

    Sorry to waste your time, but thanks again for all your help!

    E

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1