+ Reply to Thread
Results 1 to 7 of 7

Lookup formula not working

  1. #1
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Lookup formula not working

    I can't figure out why one of my columns on a search sheet is not returning data when it should be. This is the formula:
    =IFERROR(VLOOKUP(ROWS($C$11:C11),ReturnData!$B$6:$K$49475, 11, FALSE),"")
    The same formula is working fine for my other columns (with a different offset), but as soon as I got to offset 11 it won't work anymore.
    I have attached my workbook. I am trying to get the data in L column on ReturnData sheet (labeled Notes) to display on the Search sheet in Column K.
    See attached workbook.
    Thanks,
    James
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Lookup formula not working

    Your Range ends at Column K, extend to Column L and it will work
    $B$6:$K$49475
    $B$6:$L$49475

    In a VLOOKUP any col_index_num must be included in the table_array, your current table of B:K is only 10 columns wide, so 11 will return an error.

    I'm curious, why use a ROWS() count for the lookup_value, when the number column B will always be the same as that ROWS() count? It's always better to keep things simpler, for others to be able to understand the sheet and also for performance.
    Last edited by Speshul; 07-11-2014 at 12:21 PM.
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Lookup formula not working

    Because it's referencing a range that is only 10 columns wide (B:K = 10 columns)
    So it's trying to return a value from the 11th column of a range that has only 10 columns. =#REF! error
    So that should be
    =IFERROR(VLOOKUP(ROWS($C$11:C11),ReturnData!$B$6:$L$49475, 11, FALSE),"")

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Lookup formula not working

    Hi,

    Without knowing what ACTUAL results you expect any solution is not guaranteed but it seems to me that a simple

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In K11...etc should be sufficient.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Lookup formula not working

    Thank you both. I figured it must be something simple but I couldn't see it in front of me.

  6. #6
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Lookup formula not working

    There are multiple duplicate values in column F on ReturnData

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    Without knowing what ACTUAL results you expect any solution is not guaranteed but it seems to me that a simple

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In K11...etc should be sufficient.

  7. #7
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Lookup formula not working

    Quote Originally Posted by Nitefox View Post
    Thank you both.
    You're welcome.

+ 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] Lookup formula not working on certain cells
    By Fletch74 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-02-2013, 09:03 PM
  2. Using the lookup formula but is not working correctly
    By susiesc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-31-2013, 03:38 PM
  3. Replies: 3
    Last Post: 03-09-2013, 10:23 AM
  4. v lookup formula not working?
    By Lornabol in forum Excel General
    Replies: 6
    Last Post: 04-19-2011, 01:46 PM
  5. LOOKUP formula not working right
    By pdgaustintexas in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-27-2006, 11:22 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