# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  > [SOLVED] Vlookup error ["VLOOKUP evaluates to an out of bounds range" in Google Sheets]

## Jietoh

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!

----------


## Glenn Kennedy

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.

----------


## 6StringJazzer

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.

----------


## 6StringJazzer

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.
```

----------


## Jietoh

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

----------


## Jietoh

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!

----------


## 6StringJazzer

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.

----------


## mauroY

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.

----------

