Excel Help with VLookup and IF

Posted on 2012-08-24
Medium Priority
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

Dreamboat earned 1500 total points
ID: 38331846
=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

ID: 38379680
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
New style of hardware planning for Microsoft Exchange server.
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

850 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