+ Reply to Thread
Results 1 to 9 of 9

VLOOKUP Formula not working, please help!

  1. #1
    Registered User
    Join Date
    07-30-2013
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2000
    Posts
    23

    VLOOKUP Formula not working, please help!

    Hi, on attached workbook, on ‘Pivot’ sheet, I want to check to see if any cell in the A column matches any part of any cell in J:J. If a match is found I want to take the corresponding value found in L:L and display it in F:F

    I hope that’s clear, and is there any chance someone could clarify the reason why the formula that is currently in F:F is incorrect?
    Attached Files Attached Files

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,701

    Re: VLOOKUP Formula not working, please help!

    Since you are using Pivot Table in Excel 2003 and I would like to offer a tip for you...

    In your attached File just place the cursor in B3 Cell and Click on the Text Data and hold and drag it to Total Text That is C3 and leave it then you will get the data which looks like the below attachment file.

    Refer the attached file for vlookup formula and change in Pivot layout...
    Attached Files Attached Files


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: VLOOKUP Formula not working, please help!

    Perhaps.

    =IFERROR(INDEX($L$4:$L$47,MATCH(LEFT(A4,13),$J$4:$J$47,0)),"")

    Edit: For Excel<2007

    =IF(ISERROR(INDEX($L$4:$L$47,MATCH(LEFT(A4,13),$J$4:$J$47,0))),"",INDEX($L$4:$L$47,MATCH(LEFT(A4,13),$J$4:$J$47,0)))
    Last edited by Fotis1991; 08-27-2013 at 04:00 AM. Reason: Edit
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

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

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: VLOOKUP Formula not working, please help!

    Press F2 on the cells with formula shows #N/A
    You will see that some points to an empty cell and for some there is not match in your lookup table.
    Go to Report Layot of your PT and click Repeat all item.
    This will solve some of your problems( not hte one when you have not match)

    BTW your firmula should look like this
    =VLOOKUP(A4,J:L,3,0)
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  5. #5
    Forum Contributor
    Join Date
    08-11-2012
    Location
    bengalur
    MS-Off Ver
    Excel 2003, 2007
    Posts
    152

    Re: VLOOKUP Formula not working, please help!

    Your formula is fine, just needed to tune it

    =IF(ISNA(VLOOKUP(A46,J:J,1,L:L)),"Record Not Found",VLOOKUP(A46,J:J,1,L:L))

  6. #6
    Registered User
    Join Date
    07-30-2013
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2000
    Posts
    23

    Re: VLOOKUP Formula not working, please help!

    Thanks for all the help guys! Shyamhappy, when I use your formula, I end up displaying results from J:J in F:F, is there a way so that the contents of L:L are displayed in F:F

    RobertMika, about to try yours

    Thanks Sixth Sense, the tip was hella useful!

    Fotis1991, I know it's probably because i'm a noob, but when I put your formula in it's not working!

  7. #7
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: VLOOKUP Formula not working, please help!

    ..Fotis1991, I know it's probably because i'm a noob, but when I put your formula in it's not working!
    No worries. See the example pls.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-30-2013
    Location
    Cardiff, Wales
    MS-Off Ver
    Excel 2000
    Posts
    23

    Re: VLOOKUP Formula not working, please help!

    Perfect! Job done, cheers guys! This is why this forum is one of the best

  9. #9
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: VLOOKUP Formula not working, please help!

    You are welcome(from ALL of us) and thanks for the feed back.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Vlookup formula not working
    By DKAbi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-27-2012, 10:01 AM
  2. Excel 2007 : Vlookup formula not working
    By jana1120 in forum Excel General
    Replies: 2
    Last Post: 05-16-2012, 08:19 AM
  3. Vlookup Formula not working...
    By Swambo in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-15-2011, 05:31 PM
  4. Vlookup formula not working
    By RightPlace in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-07-2011, 11:38 AM
  5. Why is my Vlookup formula not working?
    By Kleinstein in forum Excel General
    Replies: 2
    Last Post: 07-08-2010, 07:38 AM

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