+ Reply to Thread
Results 1 to 8 of 8

Vlookup error ["VLOOKUP evaluates to an out of bounds range" in Google Sheets]

  1. #1
    Forum Contributor
    Join Date
    05-31-2013
    Location
    Philadelphia, Pa
    MS-Off Ver
    Excel 2010
    Posts
    164

    Vlookup error ["VLOOKUP evaluates to an out of bounds range" in Google Sheets]

    Hello!

    Can anyone help me find the error in the following google sheets formula. The error message is "VLOOKUP evaluates to an out of bounds range". The problem is that I cannot find any issues with the formula.

    [FORMULA][/=if($A2=ʺʺ,ʺʺ,if(and(vlookup($A2,'Uniform Violation 8th and 7th grade '!$A:$FR,columns($D$1:D$1)+3,false)=ʺʺ,vlookup($A2,'Cursing 8th and 7th Grade '!$A:$FR,columns($D$1:D$1)+3,false)=ʺʺ,vlookup($A2,'Late to class 8th and 7th Grade'!$A:$FR,columns($D$1:D$1)+3,false)=ʺʺ,vlookup($A2,'Gum 8th and 7th grade'!$A:$FR,columns($D$1:D$1)+3,false)=ʺʺ,vlookup($A2,'Cutting Class/ Wandering 8th and 7th Grade '!$A:$FR,columns($D$1:D$1)+3,false)=ʺʺ,vlookup($A2,'Dismissal Violation 8th and 7th Grade'!$A:$FR,columns($D$1:D$1)+3,false)=ʺʺ,vlookup($A2,'Unwanted Cell Phone Use in Class'!$A:$FR,columns($D$1:D$1)+3,false)=ʺʺ),ʺʺ,concatenate(ʺUNIFORM: ʺ,vlookup($A2,'Uniform Violation 8th and 7th grade '!$A:$FR,columns($D$1:D$1)+3,false),CHAR(10),ʺCURSING: ʺ,vlookup($A2,'Cursing 8th and 7th Grade '!$A:$FR,columns($D$1:D$1)+3,false),CHAR(10),ʺLATE: ʺ,vlookup($A2,'Late to class 8th and 7th Grade'!$A:$FR,columns($D$1:D$1)+3,false),CHAR(10),ʺGUM: ʺ,vlookup($A2,'Gum 8th and 7th grade'!$A:$FR,columns($D$1:D$1)+3,false),CHAR(10),ʺCUTTING: ʺ,vlookup($A2,'Cutting Class/ Wandering 8th and 7th Grade '!$A:$FR,columns($D$1:D$1)+3,false),CHAR(10),ʺDISMISSAL: ʺ,vlookup($A2,'Dismissal Violation 8th and 7th Grade'!$A:$FR,columns($D$1:D$1)+3,false),CHAR(10),ʺCELL PHONE: ʺ,vlookup($A2,'Unwanted Cell Phone Use in Class'!$A:$FR,columns($D$1:D$1)+3,false))))FORMULA]

    Thanks!
    Last edited by 6StringJazzer; 10-12-2016 at 03:21 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,752

    Re: Vlookup error

    No sane person is going to try to decipher that lot.

    Will you please attach a sample Excel workbook? Please don't attach a picture of one.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,430

    Re: Vlookup error

    What cell is the formula in?

    Note that I have moved this to other platforms. Although the syntax is valid in Excel, some of the sheet names are not so this cannot be tested in Excel.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,430

    Re: Vlookup error ["VLOOKUP evaluates to an out of bounds range" in Google Sheets]

    It is not as bad as it looks when you lay it out like this. But note that the column count expression is columns($D$1:D$1). If this formula is copied to column FR, for example, it will blow the range of the lookup.

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    05-31-2013
    Location
    Philadelphia, Pa
    MS-Off Ver
    Excel 2010
    Posts
    164

    Re: Vlookup error ["VLOOKUP evaluates to an out of bounds range" in Google Sheets]

    The formula is in cell D2. I've uploaded a test file. I'm open to any suggestions that either fix my formula or completely rewrite it in favor of something better. Just remember that this has to work in google sheets.

    Thanks
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    05-31-2013
    Location
    Philadelphia, Pa
    MS-Off Ver
    Excel 2010
    Posts
    164

    Re: Vlookup error ["VLOOKUP evaluates to an out of bounds range" in Google Sheets]

    Well I fixed this issue, though honestly I don't know what the original problem was. I just rewrote the formula in piece by piece and it began working.

    After this experience, I don't think I'm going to work on google sheets ever again, this was just a nightmare. I only did it this time as a favor for a co-worker.

    Thanks all!

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,430

    Re: Vlookup error ["VLOOKUP evaluates to an out of bounds range" in Google Sheets]

    Now that I have your file, I opened it in Google sheets. First of all, your formula as pasted above has quote characters that were not parsable when pasted.

    Once I fixed that, I did not get the same error as you but I got parse errors. You have a number of issues with how you entered sheet names in your formula (extra trailing spaces in formula, sheet name in formula doesn't match actual sheet name, sheets that don't exist). Once I resolved all of those your formula worked. You probably just cleaned it all up when you re-entered the formula.

  8. #8
    Registered User
    Join Date
    03-23-2017
    Location
    Massachusetts
    MS-Off Ver
    Office 10
    Posts
    3

    Re: Vlookup error ["VLOOKUP evaluates to an out of bounds range" in Google Sheets]

    Just do the importrange first and allow access in the the pop-up box that appears when you hover over the cell with the formula. After doing that, add the vlookup.

+ 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. Concatenate error across 2 worksheets with vlookup error
    By COGICPENNY in forum Excel General
    Replies: 2
    Last Post: 11-30-2015, 07:56 PM
  2. Iferror vlookup if error vlookup if x=Y,
    By rwmeis in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2013, 08:24 PM
  3. How to error trap a vlookup that returns an error or #N/A
    By kjy1989 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-06-2013, 12:01 PM
  4. [SOLVED] lookup - if(is error(vlookup and error messages meanings
    By grphillips in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-18-2013, 07:03 PM
  5. Vlookup Error
    By WelshAL in forum Excel General
    Replies: 2
    Last Post: 11-05-2012, 07:17 AM
  6. Replies: 0
    Last Post: 05-14-2012, 11:59 PM
  7. Help with VLOOKUP error
    By Zipping2010 in forum Excel General
    Replies: 3
    Last Post: 12-11-2011, 07:34 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