Read through how the VLOOKUP function works: Microsoft Excel VLOOKUP

Create a new Excel file.

Add the following columns.

a$.$ Company Name

b$.$ Year

c$.$ Sales

d$.$ Cost of Goods Sold

e$.$ Gross Profit

f$.$ Net Income

Use VLOOKUP to populate the Company Name from your files.

a$.$ For example, if using the naming convention above, the following would populate the Pfizer name:

$=$VLOOKUP$("$Entity Registrant Name";"$[$PFE$\_10$K$\_$FY$22.$xIsx$]$Document and Entity Information'!$A:$D$,2,$ FALSE$)$

The formula looks for the cell in the first row of the array with the value "Entry Registrant Name" and returns the value in the second column of the array in the file PFE$\_10$K$\_$FY$22.$xIsx and in the tab "Document and Entity Information. In this case the value in that cell is PFIZER INC.

You can see the power of VLOOKUP to quickly pull data from different sources. One could duplicate this formula and only change the file name to pull data from many sources, assuming the files are formatted the same $-$ that is the same array, columns, tab names and lookup value apply.

Create nine total rows in your Excel file: three for each company. Use VLOOKUP to populate the company name $(3$ rows for each company$),$ and then fill in the years FY$22,$ FY$21$ and FY$20.$ Each company should have three rows and three years when completed. The Find and Replace feature in Excel $($Control H$)$ can help you quickly replace company names within the formula.

We can use the same VLOOKUP function to pull data for Sales, Cost of Goods Sold and Net Income. Look through the Excel files for the three companies and identify some barriers to using VLOOKUP. For example, VLOOKUP works best when the files are formatted the same, the tab names are the same and the lookup value are the same. Is that the case with these filos?