One of Microsoft Excel's function, VLOOKUP has been used by this group to enhance net present value calculations. Traditionally, net present value (NPV) is calculated by holding its inputs constant. What if inputs were not constant? In this group’s taxi start up business model, they have calculated NPV while not holding its input variables constant! More interesting: the variables only have to be entered with its effective date just once! This saves considerable time given the number of variables their business model takes into consideration. Alternatively, the multiple variables would be type out one by one for each month (60 months in total). Let’s look at VLOOKUP & how this group used it to generate a more realistic NPV model. (Click on the image up top, or here to access the Taxi Start Up Business Model excel file)
1. What does VLOOKUP mean?
The VLOOKUP function enable users to search for a value in the first column of a table and return the value in the same row from another column in the table.
The formula for the VLOOKUP function is:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value: This is the value that you are looking for in the first column of the table. Being either a value or a reference, the Lookup_value cannot be smaller than the smallest value in the first column of table_array or it will display the #N/A error value. Excel will search for the closest match without rounding up. Also see 'Range Lookup functionality' a few paragraphs down.
Table_array: This is the collection of rows and columns containing the list that you are searching. Either a predefined named range, or a simple cell block range (eg.: C12:G21) may be used
Col_index_num: When Excel has found a row of data matching the 'Lookup_Value', it slides to the right this number of columns and outputs / returns that cells contents,. For example, if col_index_num was set to "2", excel returns/outputs the value in the second column (one column to the right) in table_array. If the value is less than 1, the #VALUE! error will be shown and if the value is greater than the number of columns in table_array, the #REF! error will be shown.
Range_lookup: The use of this value is to find either an approximate match or an exact match to return a value from a cell. If an approximate match is desired you may type in TRUE or leave the value blank. Microsoft Excel will look into your chosen “table_array” for a value closest to your input value. If you want an exact match returned to our cell, type in FALSE. In doing so, Excel will search for the exact figure and if not found, a #NA error value will replace your cell.
2. Looking into Detail
We will describe in depth the function of Range_lookup in the VLOOKUP formula and the importance of approximate and exact match below:
1. What does VLOOKUP mean?
The VLOOKUP function enable users to search for a value in the first column of a table and return the value in the same row from another column in the table.
The formula for the VLOOKUP function is:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value: This is the value that you are looking for in the first column of the table. Being either a value or a reference, the Lookup_value cannot be smaller than the smallest value in the first column of table_array or it will display the #N/A error value. Excel will search for the closest match without rounding up. Also see 'Range Lookup functionality' a few paragraphs down.
Table_array: This is the collection of rows and columns containing the list that you are searching. Either a predefined named range, or a simple cell block range (eg.: C12:G21) may be used
Col_index_num: When Excel has found a row of data matching the 'Lookup_Value', it slides to the right this number of columns and outputs / returns that cells contents,. For example, if col_index_num was set to "2", excel returns/outputs the value in the second column (one column to the right) in table_array. If the value is less than 1, the #VALUE! error will be shown and if the value is greater than the number of columns in table_array, the #REF! error will be shown.
Range_lookup: The use of this value is to find either an approximate match or an exact match to return a value from a cell. If an approximate match is desired you may type in TRUE or leave the value blank. Microsoft Excel will look into your chosen “table_array” for a value closest to your input value. If you want an exact match returned to our cell, type in FALSE. In doing so, Excel will search for the exact figure and if not found, a #NA error value will replace your cell.
2. Looking into Detail
We will describe in depth the function of Range_lookup in the VLOOKUP formula and the importance of approximate and exact match below:
Range_lookup is an optional function in the VLOOKUP formula. An Example to the right describes how the Taxi Business worksheet have used both exact and approximate match to manage their data.
An example of where “exact match” is appropriate can be found in their group’s VLOOKUP of car model information. If the input “Honda Civic” was misspelled as “Handa Civic” approximate match will return information on Ford Focus, instead of the intended “Honda Civic”. Exact match will immediately return an error value, notifying the user that no match was found. The basis of this premise is VLOOKUP searches for the closest match without going over using numerical and alphabetical order.
An example of where “approximate match” is appropriate can be found in their group’s VLOOKUP of gas prices. Again, VLOOKUP searches for the closest match to the input data without going higher.
3. For Savvy Excel Users: Nested Formulas & Name Range
An example of where “exact match” is appropriate can be found in their group’s VLOOKUP of car model information. If the input “Honda Civic” was misspelled as “Handa Civic” approximate match will return information on Ford Focus, instead of the intended “Honda Civic”. Exact match will immediately return an error value, notifying the user that no match was found. The basis of this premise is VLOOKUP searches for the closest match without going over using numerical and alphabetical order.
An example of where “approximate match” is appropriate can be found in their group’s VLOOKUP of gas prices. Again, VLOOKUP searches for the closest match to the input data without going higher.
3. For Savvy Excel Users: Nested Formulas & Name Range
Nested Formulas: VLOOKUP can be used inside another formula. For instance, this formula is used to estimate the odometer reading "=IF(AK15<'Input Variables & Final NPV'!$E$12,AK15+(AL14*(VLOOKUP(AL12,SCH_ON_DUTY_KM_AVG,2))),""). As you can see it has a VLOOKUP command inside another formula.
What does Name Range do?
The Name Range is a user friendly tool in Microsoft Excel which allows the individual to manage their worksheet through assigning a meaningful name to a single cell or ranges of cells. The ultimate use of this prevents users from incorrectly typing in the wrong cell. For example, typing C8:C10 could be tedious so as an alternative is typing in "Taxi_Rates" when we have named the cell.
How do you use it?
1. Select the cell range (C8:C10) or a single cell that you want to name (C6).
2. Click the Name Box near the top left.
3. Type in a Name for the selection
(Note: The names cannot have spaces.)
4. After you finish typing the name, press Enter.
________________________________________________________
4. References
The Name Range is a user friendly tool in Microsoft Excel which allows the individual to manage their worksheet through assigning a meaningful name to a single cell or ranges of cells. The ultimate use of this prevents users from incorrectly typing in the wrong cell. For example, typing C8:C10 could be tedious so as an alternative is typing in "Taxi_Rates" when we have named the cell.
How do you use it?
1. Select the cell range (C8:C10) or a single cell that you want to name (C6).
2. Click the Name Box near the top left.
3. Type in a Name for the selection
(Note: The names cannot have spaces.)
4. After you finish typing the name, press Enter.
________________________________________________________
4. References
Personal Interview with BlackTop Taxi
http://www.chrysler.ca/
http://www.ford.ca/
http://www.gm.ca/
http://www.honda.ca/
http://www.mazda.ca/
http://www.toyota.ca/
http://www.yellowcabonline.com/
5. Further Readings
http://www.chrysler.ca/
http://www.ford.ca/
http://www.gm.ca/
http://www.honda.ca/
http://www.mazda.ca/
http://www.toyota.ca/
http://www.yellowcabonline.com/
5. Further Readings
Taxi Start Up Business Model*Very Helpful*
Microsoft Office Online Help with VLOOKUP
YouTube Video on VLOOKUP
6. External Links
British Columbia Institue of Technology
Business Information System 2050 Course Web Page
Microsoft Excel Home Page
6.I Practice
YouTube Video on VLOOKUP
6. External Links
British Columbia Institue of Technology
Business Information System 2050 Course Web Page
Microsoft Excel Home Page
6.I Practice
Now lets practice what we've learned about VLOOKUP. Download the file below and work through the instructions.Taxi Start Up Business Model Practice
We hope you enjoyed this tutorial on using VLOOKUP for Microsoft Excel! Please leave a comment if you have any questions .
We hope you enjoyed this tutorial on using VLOOKUP for Microsoft Excel! Please leave a comment if you have any questions .
