Workout with Excel

Comments

Post a Comment

Popular posts from this blog

Basic Excel Formulas Guide

Image
Basic Excel Formulas Guide Mastering the basic Excel formulas is critical for beginners to become highly proficient in financial analysis. Microsoft Excel is considered the industry standard piece of software in data analysis. Microsoft’s spreadsheet program also happens to be one of the most preferred software by investment bankers and financial analysts in data processing, financial modeling, and presentation. This guide will provide an overview and list of basic Excel functions. Basic Terms in Excel There are two basic ways to perform calculations in Excel: Formulas and Functions. 1. Formulas In Excel, a formula is an expression that operates on values in a range of cells or a cell. For example, =A1+A2+A3, which finds the sum of the range of values from cell A1 to cell A3. 2. Functions Functions are predefined formulas in Excel. They eliminate laborious manual entry of formulas while giving them human-friendly names. For example: =SUM(A1:A3). The function sums all the valu...
Read more

How to perform reverse VLOOKUP

Image
METHOD - 1  VLOOKUP Formula =VLOOKUP(A1,CHOOSE({3,2,1},col1,col2,col3),3,0)    Explanation  To reverse a VLOOKUP – i.e. to find the original lookup value using a VLOOKUP formula result – you can use a tricky formula based on the CHOOSE function, or more straightforward formulas based on INDEX and MATCH or XLOOKUP as explained below. In the example shown, the formula in H10 is: =VLOOKUP(G10,CHOOSE({3,2,1},B5:B8,C5:C8,D5:D8),3,0) With this setup, VLOOKUP finds the option associated with a cost of 3000, and returns "C". Note: this is a more advanced topic. If you are just getting started with VLOOKUP, start here. Introduction A key limitation of VLOOKUP is it can only lookup values to the right. In other words, the column with lookup values must be to the left of the values you want to retrieve with VLOOKUP.  As a result, with standard configuration, there is no way to use VLOOKUP to "look left" and reverse the original lookup. From the standpoint of VLOOKUP, we ca...
Read more