This video series shows you how to build an engine selection application in Excel. It starts with an overview of the finished product, and then takes you step-by-step towards that target.
The target application should in the following manner:
- User inputs number of cylinders, fuel type and required power output in kW
- Application displays engine fulfilling those requirements
Everything is built using Excel. No other tools are required.
Picking an engine from a data table can be achieved in numerous ways. Here the Vlookup function is used.
Click in the cell you wish to have the result in, and then call the Vlookup function using either the function wizard or by simply typing the name in the formula bar.
The Vlookup function requires four arguments:
Lookup-value is the value we input. It is to this value of power that we require an engine
Table_array is the database of engines we got. So here we select the whole table. Note that the data type of both the lookup_value and table_array must be identical. In our case both are engine power in bhp.
Col_index_num here we have to specify the column number in which the required parameter resides.
Range_lookup gets either true or false. With false the Vlookup function returns only exact matching values.
In part 2 we had Range_lookup, which is the final argument in the Vlookup, set to False.
This means that the Vlookup function only finds engines exactly matching the power input by the user. What if the user inputs a power value to which no excat match exists.
Keeping Range_lookup to false means no engine is selected. Setting Range_lookup to True makes the Vlookup output the next smallest engine, if no exact match is found.
That is however not what we need. We need to select the next bigger engine, because a smaller engine cannot supply the power we require.
We need a different set of functions - Match and Index.
Please use the feedback link below for comments and suggestions.