- Mike Bruno
Let V-LookUp Help!
What is V-LookUp? V-LookUp is an amazing function within Excel that can assist you in merging two reports together. It is an essential tool to use when analyzing your data. We use so many different programs each day building up various databases. Inorder to help answer some of those crucial questions to see how your business is performing we have to pull all of that data together. Most likely you may already use V-Lookup within your reports but if you have not had the pleasure in taking advantage of this Excel function as of yet here is a quick and easy how to guide with some extra tips that will show you how V-LookUp can help.
Here is a quick video showing how to use the V-LookUp Function
First the V-LookUp function looks at a unique identifier that is an identical number, text or combination of the two that is listed in both reports. Examples of unique identifiers would be; a product name, an employee name, a customer number, or a purchase order number. Once you have determined the unique identifier you can begin your V-Lookup.
Tip: Some reports that are run will put extra spaces at the end of text which will make it look as though the v-lookup function is not working. Believe it or not but those extra spaces that you may not see will make the unique identifier appear not identical so the V-Lookup Function is working but not findig any matches. This is an easy fix by using the trim function, =trim(A5) and trimming the items to remove the extra spaces.
Next add a blank column where you would like to store the data that is being pulled over. Now start your V-LookUp by adding an equal sign in the blank cell and selecting the V-LookUp Function.
Lookup_value: On the report you are pulling data into enter the column which holds the unique identifier. Make sure you lock the column in place within the formula by using $ symbol.
Tip: The unique identifier column should always come before the column you are pulling the data into.
Table_array: Enter into the Sheet you are pulling your data from and select the columns needed starting with the unique identifier as the first column selected. Make sure you lock your columns and rows if needed within the formula by using the $ symbol.
Tip: The unique identifier column should always come before the columns you are pulling the data from.
Col_index_num: Enter the number of the column that has the data you would like to pull over into the other report.
Tip: The unique identifier column always starts as the number 1 then you have to count each column after that until using the number of the column that holds the data you are pulling over.
Range_lookup: Enter False
Tip: Don't want to see #N/A when no data is found simply add an IfError function to return a blank value if no records exist; =IFERROR(VLOOKUP(A:A,Sales!A:D,2,False),"") .
There are so many different helpful techniques out there that can help minimize your time spent in creating reports and allowing you more time analyzing the data which would permit you more time to strategize on building your business. Feel free to add some of your tips on what tools you find usefull when gathering your data.