Embedded VLOOKUP function within IF function

Discussion in 'Microsoft Excel Worksheet Functions' started by beautyteknorth, Aug 16, 2006.

  1. Please help!

    =IF(VLOOKUP(F252,'YB Engineer planning'!G:H,2,FALSE)="Olivetti
    Decommissioning",VLOOKUP(F252,'YB Engineer planning'!A:H,3,FALSE),"")

    I am trying to lookup two lots of data - one is a filter - "Olivetti
    Decommissioning" - then the site name is the second lookup provided the
    work is "Olivetti Decommissioning".

    The formula above works perfectly well for 95% of my data but for
    reasons best known to someone else there are 5 lookup entries that
    result in a blank cell (which is stated in the formula if there are no
    entries for the site name and "Olivetti Decommissioning"). These 5
    entries ARE available on the Engineer planning worksheet. Most of the
    entries have more than one entry on the Engineer planning sheet but it
    is just the five that are not getting a result. I know that the formula
    is referencing properly because I get an #N/A if I change the names
    slightly.

    I have ensured that the lookup data is spelt the same. I cannot see
    what the issue is? Any ideas? Thanks in advance!

    :confused:

    Amanda


    --
    beautyteknorth
    ------------------------------------------------------------------------
    beautyteknorth's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37585
    View this thread: http://www.excelforum.com/showthread.php?threadid=572113
     
    beautyteknorth, Aug 16, 2006
    #1
    1. Advertisements

  2. beautyteknorth

    Guest Guest

    to debug this, I would use the match() function to see what line is being
    found in both sections.
    =match(F252,'YB Engineer planning'!G:G)
    =match(F252,'YB Engineer planning'!A:A)
    check over the appropriate number of columns and see what you get.

    "beautyteknorth" wrote:

    >
    > Please help!
    >
    > =IF(VLOOKUP(F252,'YB Engineer planning'!G:H,2,FALSE)="Olivetti
    > Decommissioning",VLOOKUP(F252,'YB Engineer planning'!A:H,3,FALSE),"")
    >
    > I am trying to lookup two lots of data - one is a filter - "Olivetti
    > Decommissioning" - then the site name is the second lookup provided the
    > work is "Olivetti Decommissioning".
    >
    > The formula above works perfectly well for 95% of my data but for
    > reasons best known to someone else there are 5 lookup entries that
    > result in a blank cell (which is stated in the formula if there are no
    > entries for the site name and "Olivetti Decommissioning"). These 5
    > entries ARE available on the Engineer planning worksheet. Most of the
    > entries have more than one entry on the Engineer planning sheet but it
    > is just the five that are not getting a result. I know that the formula
    > is referencing properly because I get an #N/A if I change the names
    > slightly.
    >
    > I have ensured that the lookup data is spelt the same. I cannot see
    > what the issue is? Any ideas? Thanks in advance!
    >
    > :confused:
    >
    > Amanda
    >
    >
    > --
    > beautyteknorth
    > ------------------------------------------------------------------------
    > beautyteknorth's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37585
    > View this thread: http://www.excelforum.com/showthread.php?threadid=572113
    >
    >
     
    Guest, Aug 16, 2006
    #2
    1. Advertisements

  3. Hi bj,

    I've tried this - there are multiple occurances of the same value in
    the list and the list is unsorted so if I omit the match type the
    function returns some random value and if I include the match type it
    accurately returns the first instance of the value in the list.

    I don't get why only 5 values are affected!!

    Thanks for your help.

    Amanda


    --
    beautyteknorth
    ------------------------------------------------------------------------
    beautyteknorth's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37585
    View this thread: http://www.excelforum.com/showthread.php?threadid=572113
     
    beautyteknorth, Aug 16, 2006
    #3
  4. beautyteknorth

    Dav Guest

    Dav, Aug 16, 2006
    #4
  5. beautyteknorth

    Guest Guest

    thanks Dav, I knew better (or should have)

    "Dav" wrote:

    >
    > try what bj said but use
    > =match(F252,'YB Engineer planning'!G:G,0)
    > =match(F252,'YB Engineer planning'!A:A,0)
    >
    > to ensure exact matches.
    >
    > Check for extra spaces after the text strings, they could be causing
    > you problems
    >
    > Regards
    >
    > Dav
    >
    >
    > --
    > Dav
    > ------------------------------------------------------------------------
    > Dav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27107
    > View this thread: http://www.excelforum.com/showthread.php?threadid=572113
    >
    >
     
    Guest, Aug 16, 2006
    #5
  6. beautyteknorth, Aug 17, 2006
    #6
  7. beautyteknorth

    Dav Guest

    If you are using a match type 0, which is the same as your vlookup in
    essence it will always return the first match.

    You maybe have to restate your problem giving an example and showing
    what you want exactly as the solution. In your example

    =IF(VLOOKUP(F252,'YB Engineer planning'!G:H,2,FALSE)="Olivetti
    Decommissioning",VLOOKUP(F252,'YB Engineer planning'!A:H,3,FALSE),"")

    If the first match of f252 in columnG has olivetti Decommissioning next
    to it you are then saying rematch this value in f252 in columnA and
    return the value in column c next to it.

    If column A is different to column G you will not be returning the same
    row

    What exactly do you want to achieve?

    Regards

    Dav


    --
    Dav
    ------------------------------------------------------------------------
    Dav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27107
    View this thread: http://www.excelforum.com/showthread.php?threadid=572113
     
    Dav, Aug 17, 2006
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. James Lee

    Vlookup within a vlookup function

    James Lee, Mar 31, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    7
    Views:
    315
    Arvi Laanemets
    Apr 1, 2004
  2. jesseb

    Can I insert a function within the VLOOKUP function?? please help

    jesseb, Oct 19, 2004, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    297
    Alex Delamain
    Oct 19, 2004
  3. Guest

    IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP(

    Guest, Jan 26, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    1,208
  4. Guest

    Embedded If Function in a Vlookup Function

    Guest, Jan 13, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    146
    Simon Murphy
    Jan 13, 2007
  5. VivienW

    VLOOKUP within VLOOKUP

    VivienW, Jun 1, 2009, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    528
    Sheeloo
    Jun 1, 2009
Loading...

Share This Page