Optimization using data validation tool in excel

Subscribe my youtube channel!


In the previous post you learn how to use Vlookup using 2 lookup tables.

In this post, using the same data, i want to make otimization of the data table search (blue table) , that returns the room price in function oh Hotel name, Number of person and Season:


To this 3 input parameters (Hotel name, Number of person and Season) we used editable cells (A11,B11,C11) where we could write any value/text.

In this post i’m gonna transform this 3 cells in 3 drop down lists with the purpose of just allowing the possible value (cell A11= 1;2 / cell B11= low;high and cell C11= Hotel Summer;Hotel Madrid; Hotel Village) and reject other values. To make this change i use data validation tool (list):


After choose data validation, select list and in source write the two possible value for number people(1 and 2 separated by semicolon):



To not allow other values, go to error message and write title for the error window appear when the value is not valid:


The final result is the drop down list with the 2 possible values:


When values are invalid gives the error message:


After configure the first drop drow box, you can configure the others:



Now you have all the 3 input cells configured with drop drow lists, giving a professional aspect to the table.

Now the final change. If you remember you use a IF function to choose what table to choose (low or high season):


The logical_test used in the IF function in the previous post was:

ISNUMBER(SEARCH(B11;A1))  -> see if text B11 appears in text A1

A way to simplify this test is seeing the lenght of cell B11 (if lenght of B11 is equal to 3, we have “low” in cell B11; if is not 3 the cell has the value “high”):

LEN(B11)=3 is the logical test, and the final result is:


As you can see, excel as the hability to use different methods, getting at the final the same results.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s