Excel Help with VLookup and IF

Posted on 2012-08-24
Last Modified: 2012-09-08
Hi there,

Looking for some Excel expertise - I am a rookie in terms of Excel.  Here's what I am trying to do:

I have a column of cells with a pull down list that each correspond to a dollar value.  I have the VLookup function referencing a table, and it all works fine.  You choose the correct item from the pull down, and the correct dollar figure is placed into the next cell.  What I would like, is to have the last item in the list as 'other', and have the user prompted for a dollar figure to enter, as well as being promped for a text string which would then be placed in another cell.

Am I asking too much?
Question by:mphackett
    LVL 22

    Accepted Solution

    =if(B1="Other","Enter value",vlookup(B1,Range,Column,0))

    I'm not sure how you could make it any nicer than that because you've got your vlookup AND an entered value in the same column. Kinda yucky.

    In the "description cell", you can conditionally format it so that when B1 says "Other" AND the description cell is blank, it can be yellow filled (to alert entry). Once description entered, it return to normal fill (no fill).

    The problem with this whole setup is: what happens if someone chooses Other, then puts a price in, and they were wrong. Now, they want to remove the "other", and the formula isn't automatically reset to do the vlookup again.... no error handling.

    Might want to think about a macro that creates a lookup row every time you enter a quantity in, say, column A. That way, if you mess up, you just clear the quantity in A and your row is "refreshed" so to speak.

    Would require VBA.

    Author Closing Comment

    Thanks very much for your input, I had to re-tool the way I put the spreadsheet together, and believe I've come up with the best solution.

    Thanks again,


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now