+ Reply to Thread
Results 1 to 4 of 4

Vlookup Error "The cell currently being evaluated contains a constant"

  1. 05-19-2017, 05:16 PM #1
    IAntoniazi
    IAntoniazi is offline
    Registered User
    Join Date
    05-19-2017
    Location
    Queens, NY
    MS-Off Ver
    2010
    Posts
    2

    Vlookup Error "The cell currently being evaluated contains a constant"

    Hello,

    I'm having a problem with a VLookup function. I'm trying to compare values from 2 columns and return the value in a third column where the match is found. So I have entered:

    =Vlookup(C2, Sheet1!A:B, 1, FALSE)

    Column C on the current Sheet contains the values to look up, the corresponding values are in Column B on Sheet1 and I want to return the values from column A on Sheet1. I'm getting a #N/A error and when I view the calculation steps, I'm getting the following statement: The cell currently evaluate contains a constant."

    I have viewed a number of other threads on this topic and tried the solutions suggested there. I can confirm:
    - All cells in all 3 relevant column are formatted as "General"
    - Column B in Sheet 1 is sorted ascending
    - Column B definitely contains the values in question. I've spot-checked a few of them.
    - I have read that copy-pasting can generate this error. Sheet1 is pasted from another Workbook. However, I have tried the same formula referencing the original workbook, instead of Sheet1, and had the same result.
    - I have re-entered the formula after making any changes in the relevant columns

    I'm afraid the sheet contains sensitive information about our clients, so I cannot post it here. If anyone has any suggestions as to why this error persists, I would greatly appreciate your input.

    Thanks!
    Reply With QuoteRegister To Reply
  2. 05-19-2017, 05:22 PM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,728

    Re: Vlookup Error "The cell currently being evaluated contains a constant"

    Reverse VLOOKUPs are possible, but awkward. Try this instead:

    =INDEX(Sheet1!A:A,MATCH(C2,B:B,0))
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 05-19-2017, 05:25 PM #3
    Richard Buttrey
    Richard Buttrey is offline
    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: Vlookup Error "The cell currently being evaluated contains a constant"

    The value to be looked up, i.e. C2 must exist in the first column of the range you specify - which in your case is A:B.
    Instead either change your table and swap columns A & B or use an INDEX(MATCH()) combination.

    Incidentally try to avoid specifying whole columns. That can often cause things to slow down.

    If you need further help then upload the workbook, or at least a representative sub set of it.
    In the best Dragnet tradition (yes I really am that old), change the names to protect the innocent. We really don't mind how many D. Ducks or M .Mouse you have.
    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.
    Reply With QuoteRegister To Reply
  4. 05-23-2017, 11:12 AM #4
    IAntoniazi
    IAntoniazi is offline
    Registered User
    Join Date
    05-19-2017
    Location
    Queens, NY
    MS-Off Ver
    2010
    Posts
    2

    Re: Vlookup Error "The cell currently being evaluated contains a constant"

    I didn't realize the looked-for value had to be in the left-most column! I switched A and B and now it works great. Thanks everyone!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] "constant expression required" error given when setting up an array
    By luv2glyd in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-27-2017, 12:06 PM
  2. Problem:- "the cell currently being evaluated contains a constant"
    By Terressa in forum Excel General
    Replies: 1
    Last Post: 07-15-2015, 01:42 AM
  3. [SOLVED] "ByRef:mismatch" and "Constant Expression Required" Errors
    By Mishil in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-10-2015, 06:47 PM
  4. Don't know the Forumlas with problem either using "vlookup" "if" "and" or "index" "match"
    By Jokeman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  5. [SOLVED] "The cell being evaluated currently contains a constant". PLS HELP!
    By tpneal in forum Excel General
    Replies: 2
    Last Post: 04-20-2012, 10:20 PM
  6. Need column evaluated to count total of occurrences of "Y". How?
    By learning in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-11-2006, 09:00 PM
  7. [SOLVED] the cell currently being evaluated contains a constant
    By Barrie Wells in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 12:05 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1