Vlookup, Index/Match : PDF to Excel and Financial Modeling

Vlookup and other lookup excel funnctions are very popular among financial analysts. Financial analysts and corporate accountants maintain financial models to make various forecasts, usually spanning from free cash flow, sale forecast, various types of financial ratios tables. They also develop excel models for predictions of trends in business operations. Such trends may include the percentage of revenue that goes into a sales channel or into a particular area of business or the percentage of revenue that has been generated from a marketing campaign. Vlookup is a very recurrent excel function for these models.  In all of the above situations the ingested data in the model con both come from internal databases as well as printed and digital PDF documents. Tabex offers the flexibility to convert pdf table to excel but also capture digital data from databases and web sources. In this respect it increases the analyst productivity by eliminating tedious copy and paste as well as re-formatting operations. However, analysts invest time, not only in converting PDf to excel and cop&paste data entry from digital tables, but also in looking up the data and insert them into excel.

Here we have aggregated a list of excel functions that can further save your valuable time when working together the pdf to excel convertor for financial analysts

 

SUMIF

You use the SUMIF function to sum the values in a range that meet criteria that you specify. This is useful when ingesting a new sheet in your excel work flow from a pdf to excel converter. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5. You can use the following formula:

=SUMIF(B2:B25,”>5″)

In this example, the criteria is applied the same values that are being summed. If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, “John”, C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal “John.”

VLOOKUP

You can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range. For example, suppose that you have a list of employees contained in the range A2:C10. The employees’ ID numbers are stored in the first column of the range, as shown in the following illustration.

If you know the employee’s ID number, you can use the VLOOKUP function to return either the department or the name of that employee. To obtain the name of employee number 38, you can use the formula =VLOOKUP(38, A2:C10, 3, FALSE). This formula searches for the value 38 in the first column of the range A2:C10, and then returns the value that is contained in the third column of the range and on the same row as the lookup value (“Axel Delgado”).

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

HLOOKUP

Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.

The H in HLOOKUP stands for “Horizontal.”

Syntax

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

 

INDEX/MATCH

This is a combination used instead of the lookup functions when for example the number of column changes. This quite important when considering to use pdf to excel converter. In fact by the very nature of the documents scanned or the images captured on a screen the number of columns will change form document to document and capture to capture. It may be more effective to use the following syntax:

=INDEX(SelectColumnContainingWhatYouWantToReturn, MATCH(TheSingleCellYouAreLookingUp,ColumnWhereYouAreLooking,0),0)

 

These are the benefits of this approach.

  1. a)      You can “lookup to the left”
  2. b)      You don’t get incorrect results when a column is inserted or deleted from your data

c)      When used in conjunction with PDF Table converter, or other pdf to excel software you can save additional time to stream line your models.

Recommended Posts

Leave a Comment