Siegel, Here Are Your Articles for Monday, March 30, 2020
Is this email not displaying correctly?
View it in your browser .
Our Website Contact Us About Us Services Tutorials Acumatica
Share Save

Using Excel's VLOOKUP Function for Exact Matches

 

Many Excel spreadsheets contain intricate cross-referencing of data, especially from lists of data. This ability to interrelate data together streamlines number-crunching, but many uses are still relying on brute-force methods by pointing directly to each number or piece of text.  Lookup formulas, such as Excel's VLOOKUP function, can provide a paradigm shift in how you interact with Excel.

As shown in the figure, many users rely on direct cell references, as shown in cell B2. There's a fair amount of manual effort involved in creating such formulas:

  • Type an equal sign to start the formula.
  • Use your mouse or the arrow keys to locate the column where the account number exists.
  • Use your mouse or the arrow keys to track across the row to link to the related piece of data.

This isn't hard to do a few times, but when you need to link to data within a list dozens or more times, then the nature of the repetitive work can fritter away your day, as well as introduce the risk of spreadsheet errors from choosing the wrong cell.

Excel's VLOOKUP function gives us the ability to automate this process. The V in VLOOKUP stands for vertical, meaning the formula you create will look vertically down a column for a piece of information. When it finds a match, Excel will then return a second piece of data from the column that you specify. As shown in Figure 1, you'll need to provide 4 pieces of information:

  • lookup_value:ulas, such as Excel's VLOOKUP function, offer theabiltcertainly nothing wrong with this approach, but wh and then point to another cell elsewhere to c toadsheets are comprised of intricate cross-referencing of data. Linking data betwe This is where we specify that we're looking for. In this situation we're looking to find a match on the value in cell A3.
  • Table_array: This is simply a technical term for the range of cells VLOOKUP should use as its source. Notice that the table_array need only include the column that you which to return data from. This can save you time when working with large data sets as you won't end up selecting data that will never be referenced by the formula.
  • col_index_num: This is the column within the table array that you wish to return data from. In this case, 5 indicates that we want data from the fifth column, which corresponds to the month of March.
  • range_lookup : This argument controls whether Excel seeks an exact match, or an approximate match. In this case the formula shows FALSE to indicate that we want exactly the data associated with account 40100, not an account number that is close to 40100. Experienced users often use a zero in place of the word FALSE. Either are acceptable but zero is easier to type.

 
Share Save

Your Comments

Siegel Solutions Inc
Siegel Solutions Inc
(781) 487-7000
info@siegelsolutions.com
144 Gould Street Suite 205
Needham, MA 02494
Friend Me on Facebook
Follow Me on Twitter
Connect with me on LinkedIn
Saved Articles
Comments and Feedback
Refer A Friend
Your Privacy
Our firm provides the information in this e-newsletter for general guidance only, and does not constitute the provision of legal advice, tax advice, accounting services, investment advice, or professional consulting of any kind. The information provided herein should not be used as a substitute for consultation with professional tax, accounting, legal, or other competent advisers. Before making any decision or taking any action, you should consult a professional adviser who has been provided with all pertinent facts relevant to your particular situation. Tax articles in this e-newsletter are not intended to be used, and cannot be used by any taxpayer, for the purpose of avoiding accuracy-related penalties that may be imposed on the taxpayer. The information is provided "as is," with no assurance or guarantee of completeness, accuracy, or timeliness of the information, and without warranty of any kind, express or implied, including but not limited to warranties of performance, merchantability, and fitness for a particular purpose.
Powered by
Copyright © IndustryNewsletters All rights reserved.

This email was sent to: jeff@siegelsolutions.com

Mailing address: 144 Gould Street Suite 205, Needham, MA 02494