Question

In: Accounting

discussing advanced functions in Excel (finance functions, If, nested If, Vlookup, Hlookup, etc.). Please research additional...

discussing advanced functions in Excel (finance functions, If, nested If, Vlookup, Hlookup, etc.). Please research additional informative sources in order to increase your understanding of these, or any other, advanced functions. Subsequently, choose an advanced function and write down to educate regarding the meaning and application of it.

Solutions

Expert Solution

VLOOKUP & HLOOKUP for Dynamic Lookups

These two functions are easy to write, understand, and they’re a major time-saver for simpler lookups.

The VLOOKUP function can search for a specific category or value in a table and return the “opposing” value from adjacent columns.

HLOOKUP works exactly in the same way except that it looks horizontally rather than vertically.

“V” in VLOOKUP refers to vertical and “H” in HLOOKUP refers to horizontal.

If the data in your table has column headers, use the VLOOKUP function. If the data table has row headers and you need to search horizontally across the rows for a “match,” then use HLOOKUP.

The main limitation is that the value looked with VLOOKUP needs to be in the left-most table column and in HLOOKUP it has to be in the top row of the data set.

HLOOKUP is used to search along the top row and then look down to retrieve the result from a specified row – the second row in this instance.

The VLOOKUP formula syntax is the following

  • Lookup_value: A11 or East_01 (in this example).
  • Table_array: the range includes the location of the column lookup value and the columns where the return value is located (i.e. A1:B9).
  • Col_index_num: the number of columns it should move to the right. In this case, the range contains two columns with the match found in the second column.
  • Range_lookup: this argument is important to use correctly if the lookup value is text and non-numeric. If the parameter is left empty, it defaults to “TRUE” meaning that an exact match is unnecessary. But, to work properly, the lookup column must be sorted in ascending order. Otherwise, an error or #NA will appear in the cell if the column isn’t sorted in ascending order and this argument
is left empty. 
In the example above, if FALSE is excluded from the last argument then the formula will return an #NA error, because the lookup column is not sorted in ascending order. (Text shouldn’t include trailing spaces). Use the trim function to “clean up” your text to avoid this from happening.

Limitations of VLOOKUP

One major limitation of VLOOKUP is that it cannot look to the left. The values to lookup must always be on the left-most column of the range and the values to return must be on the right hand side.

You cannot use the standard VLOOKUP to look at the columns and the rows to find an exact match. The INDEX and MATCH functions are used in this instance and are much more flexible than the classical VLOOKUP.

............sk


Related Solutions

Please include some graphs and explanations. The site contains multiple informative videos discussing advanced functions in...
Please include some graphs and explanations. The site contains multiple informative videos discussing advanced functions in Excel (finance functions, If, nested If, Vlookup, Hlookup, etc.). Please view and research additional informative sources in order to increase your understanding of these, or any other, advanced functions. Subsequently, choose an advanced function and take the opportunity to educate your peers regarding the meaning and application of it.
Using the Vehicle Ratings Excel file, create formulas using nested IF, AND, and OR functions to...
Using the Vehicle Ratings Excel file, create formulas using nested IF, AND, and OR functions to implement the three rating schemes described on the spreadsheet. Rating 1 If the vehicle has A/C and a sunroof or it is newer than 2013, then YES, otherwise NO. Rating 2 If the vehicle is Red and does not have high miles, then YES, otherwise if it is a Ford or Chevy, MAYBE, otherwise NO. Rating 3 If the vehicle is older than 2013...
I am working on a research paper for my Advanced Financial Accounting class. Please provide additional...
I am working on a research paper for my Advanced Financial Accounting class. Please provide additional insight in descriptive detail on the following: Q1: What types of entities are referred to as special-purpose entities, and how have they been generally used? Q2: What is meant by a noncontrolling interest in a subsidiary? Q3: Are consolidated financial statements likely to be more useful to the creditors of the parent company or the creditors of the subsidiaries? Why or why not?
Please provide specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) etc...... Given the following information for Bajor...
Please provide specific Excel functions =NPV(…), =IRR(…), =AVERAGE(…), =YIELD(…) etc...... Given the following information for Bajor Co.: Debt: Bajor’s long-term debt capital consists of bonds with 6.250 percent coupon rate (semiannual coupon payments), 9 years time-to-maturity, and current price of 106.61 percent of its par value (i.e., price = 106.61 relative to full amount redemption par of 100). Preferred stock: Bajor has not issued any preferred stocks. Common stock (equity): Bajor’s equity capital consists of common stocks with the most...
Write a report on Excel functions and formulas most commonly used in finance for solving finance...
Write a report on Excel functions and formulas most commonly used in finance for solving finance problems (at least 5 functions). In the report, please introduce at least one example for each function used. Also, please specify the contribution of each member in group.
Please use Microsoft excel. Using a Nested Loop, what is the equation to solve for letter...
Please use Microsoft excel. Using a Nested Loop, what is the equation to solve for letter grade Nested loop Student ID Grade Letter Grade 1 5 If score is Then return 2 55 Greater than 89 A 3 86 From 80 to 89 B 5 64 From 70 to 79 C 6 25 from 60 to 69 D 7 56 less than 60 F 8 58 9 99 10 90 11 28
W72A) I am learning EXCEL Functions. Please answer in EXCEL Functions in detail Stock Returns (relationship...
W72A) I am learning EXCEL Functions. Please answer in EXCEL Functions in detail Stock Returns (relationship between hypothesis testing and confidence intervals) Suppose you as an investor with a stock portfolio of hundreds of thousands of dollars decide to sue your broker because of low returns due to lack of portfolio diversification, i.e., too many holdings with similar return prospects. The 39 monthly returns, expressed as percentages, are shown in the table below and reproduced in your Excel answer template....
this is for my advanced corporate finance class.. please give a broad and clear explanation on...
this is for my advanced corporate finance class.. please give a broad and clear explanation on this question. Describe the clientele effectin demand for dividend paying stocks. What sort of investors should wish to hold dividend paying stocks and which should not? Why? Describe Modigliani & Miller's irrelevance Propositionon dividend policy. Provide a plausible argument to explain why this proposition should hold. Explain the possible advantages and disadvantages of paying out funds through dividends or share repurchase. Describe the evolution...
Show how to answer this in EXCEL ONLY, NO megastat or minitab, etc.. Please highlight what...
Show how to answer this in EXCEL ONLY, NO megastat or minitab, etc.. Please highlight what data tools or formulas were used. A survey investigated the public’s attitude toward the federal deficit. Each sampled citizen was classified as to whether he or she felt the government should reduce the deficit or increase the deficit, or if the individual had no opinion. The sample results of the study by gender are reported to below. Gender Reduce the Deficit Increase the Deficit...
Please compute the additional payment. also please include the formula used in excel. Thanks! Auto Loan...
Please compute the additional payment. also please include the formula used in excel. Thanks! Auto Loan Payment Calculator Inputs Auto Loan Amount $20,000 Annual Interest Rate 8.50% Term of Loan in Years 3 First Payment Date 2/1/18 Last Payment Date 1/1/21 Frequency of Payment Monthly Summary Number of Payments 36 Rate (per period) 0.708% Payment (per period) $631.35 Total Interest $2,728.63 Total Payments $22,728.63 No. Due Date Payment Due Additional Payment Interest Principal Balance $20,000.00 1 2/1/18 631.35 0.00 141.67...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT