+ Reply to Thread
Results 1 to 11 of 11

Excel 2007 : hlookup returning #NA

  1. #1
    Forum Contributor
    Join Date
    04-20-2011
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2007
    Posts
    121

    hlookup returning #NA

    Formula in cell A2 [=HLOOKUP(A1,'Dates'!C33:P33,'Dates'!34)] result = #NA

    I am trying to read from a row in a sheet called 'Dates' cells C33 to P33 and return a value from 'Dates' cells C34 to P34 where the match is found. Row 33 has Days, Row 34 has corresponding dates.

    The sheet I am entering the formula into is at cell A2. A1 contains Sunday (B1Monday, C1 Tuesday etc)

    The formula should identify the same day in 'Dates' and return the date into cell A2. Result =#NA.

    Can anyone advise please.
    Chambo1160

  2. #2
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: hlookup returning #NA

    the ranges in fomula are not correctly given
    and hlookup only will not give the required resut you have to use the combination of functions

    Upload a sample workbook with sample data and your desired results

    In order to attach a file Click the Go Advanced button, and click on the Manage Attachments button
    Azam
    If you want to say Thank you to a member, click the reputation icon (Star) in the left bottom of the post.
    For prompt answer, be descriptive, concise, short, direct, and to-the-point.

  3. #3
    Forum Contributor
    Join Date
    04-20-2011
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2007
    Posts
    121

    Re: hlookup returning #NA

    Attachment included. I Have simplified the data and the formula is now

    [=HLOOKUP(A1,Sheet1!$A$1:$AM$1,Sheet1!$A$2:$AM$2)]

    Sheet 2 row 2 is where I need to use the formula. Result is now a #REF!


    Many thanks


    Chambo

  4. #4
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: hlookup returning #NA

    there is no attatchement

    In order to attach a file Click the Go Advanced button, and click on the Manage Attachments button

  5. #5
    Forum Contributor
    Join Date
    04-20-2011
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2007
    Posts
    121

    Re: hlookup returning #NA

    Thats weird. Here it is again, hopefully,

    Cheers
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: hlookup returning #NA

    Use
    =HLOOKUP(A1,Sheet1!$A$1:$AQ$2,2,0)

    however the date will repeat after a week when you apply the formula on the same day

  7. #7
    Forum Contributor
    Join Date
    04-20-2011
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2007
    Posts
    121

    Re: hlookup returning #NA

    Thats a good start but have a look at the result. Its part way there. Can you help any further and thanks for the help so far

    Chambo
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: hlookup returning #NA

    Hi chambo1160
    when you write a day of week e.g. Monday the excel do not know the month it belongs and it just search of Monday not for month

    you have to have some other criteria or logic to lookup

  9. #9
    Forum Contributor
    Join Date
    04-20-2011
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2007
    Posts
    121

    Re: hlookup returning #NA

    Quote Originally Posted by Martin Chamberlin View Post
    Thats a good start but have a look at the result. Its part way there. Can you help any further and thanks for the help so far

    Chambo
    Thanks again. I love Excel, it challenges the mind. I will work on this one a little further.

    Best Regards


    Chambo

    I have looked at this a different way and still returning #REF. The new attached sheet is trying to read a date from sheet 1(Forecast) and return the value below the date into sheet 2(Result). Any ideas?

    Cheers


    Chambo
    Attached Files Attached Files
    Last edited by Martin Chamberlin; 10-19-2011 at 06:00 PM.

  10. #10
    Forum Contributor
    Join Date
    04-20-2011
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2007
    Posts
    121

    Re: hlookup returning #NA

    Quote Originally Posted by Martin Chamberlin View Post
    Thanks again. I love Excel, it challenges the mind. I will work on this one a little further.

    Best Regards


    Chambo

    I have looked at this a different way and still returning #REF. The new attached sheet is trying to read a date from sheet 1(Forecast) and return the value below the date into sheet 2(Result). Any ideas?

    Cheers


    Chambo
    I have edited the sheet to simplify it hoping this gets a formula that will work for me. Please have a look, Thanks again, Chambo

  11. #11
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: hlookup returning #NA

    use
    =HLOOKUP(A2,Forecast!$A$2:$Z$3,2,0)

    i also suggest to read the example givne in the excel help for Hlookup. press F1 for help.

+ 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