VLOOKUP anamoly?

New Contributor

How can a VLOOKUP work fine in one column (C) of my worksheet but returns a #REF! error in another(F)?  Using data from another sheet in the workbook, I compile a monthly report for billing. However, recently the formula is failing.  The original spreadsheet was done in Excel 2010, but is now in Excel 2016.



Sheet name:  webdata

     A                             B              C

Domain                   DBsize  Bandwidth

website.com,           1.01        1.11

another.com            0.94        0.15

athirdsite.com          2.25        2.02


Sheet name: report

Row:  Domain, type, DBsize, FilesMB, Total, Bandwidth

In cell C6:  =VLOOKUP(A6,webdata!$A$2:$B$122,2,)

In cell F6:  =VLOOKUP(A6,webdata!$A$2:$B$122,3,)


         A                     B            C                  D              E             F

5 Domain name   Type  MySQL(MB)  Files(MB)  Total  Bandwidth(MB)

6 website.com        S            1.01             973.9        975          #REF!

7 another.com                                                                            #REF!

8 athirdsite.com                                                                          #REF!






4 Replies
Hello! It looks good, but the vlookups look weird without the missing fourth argument in both functions. Try to add a 0 (zero) for exactly searching or a 1 for stepwise searching. In your first =VLOOKUP(A6,webdata!$A$2:$B$122,2,0). Hope that helps, Greets, Eva

Regardless if the trailing 0 is there or not, it still returns the same error:  

"The cell currently being evaluated contains a constant."  







check the format of the webdata columns are the same as they were before you went to 2016?


@dmgaddy8247 wrote:

In cell F6:  =VLOOKUP(A6,webdata!$A$2:$B$122,3,)


Your matrix is two columns wide. So how do you expect a return value from column 3?



Related Conversations
pull data based on dropdown list
bbombb in Excel on
1 Replies
Vlookup formula - partial text search
fredericomarquez in Excel on
2 Replies
URGENT Excel question HELP
RileyCDeason in Excel on
1 Replies
Need FORMULA help please!!
marislav in Excel on
4 Replies
Sum of Vlookup results
dleesBLX in Excel on
1 Replies
BowlingAlleyBob in Excel on
3 Replies