CBA Sage (Peachtree) Accounting Support Logo



Linkedin Rss Gmail


Buy Sage 50 2022 Sage Certified Master Consultant logo

Menu
  • Home
  • Products
    • Sage 50 2023
      • Sage 50cloud Pro 2023
      • Sage 50cloud Premium 2023
      • Sage 50cloud Quantum 2023
      • Quantum Manufacturing 2023
      • Feature Comparison 2023
      • Sage Subscription FAQ 2023
    • Sage 50 2022
      • Sage 50cloud Pro 2022
      • Sage 50 Pro 2022
      • Sage 50cloud Premium 2022
      • Sage 50 Premium 2022
      • Sage 50cloud Quantum 2022
      • Sage 50 Quantum 2022
      • Quantum Manufacturing 2022
      • Feature Comparison 2022
      • Sage Subscription FAQ 2022
    • QuickBooks
    • Sage Business Care
    • Logicim XLGL
    • Checks & Forms
    • Why Sage 50?
    • Merchant Services
    • Add-Ons from Sage Partners
    • Sage 50 Free Trial
  • Services
    • Sage 50 Custom Reports
    • Consulting
    • Training
      • Training - Create your own Class
      • Webinars
    • Bookkeeping
    • Support
      • Technical Support
      • Implementations
      • Rebuilds & Repair
      • BI Reports
      • Custom Financials
      • Crystal Reports
    • Merchant Services
    • Custom Macros
  • Resources
    • Testimonials
    • Video & Tutorials
      • Sage Advisor
      • Sage 50 Tutorials
    • CBA Blog
    • EXCEL TIPS
    • 2023 System Reqs
    • 2022 System Reqs
    • Downloads
    • Newsletter Signup
    • Ask an Expert Form
    • Sage FAQ
      • Whats New In Sage 50?
    • Install Info
  • About Us
    • Why Buy from CBA?
    • Customer Success!
    • Careers With CBA
    • States We Serve
  • Contact
  • Site Map
CBA Green Bar
Resources > EXCEL TIPS

Welcome to the Computerized Business Blog please feel free to look around.


VLOOKUP with the Approximate Match
18
 10/18/2012 2:07:05 PM

Ever used the Vlookup with the approximate match? In this example we demonstrate how, by using the Vlookup with the approximate match, one can extract the correct trade discount percentage based on the quantity purchased.

If TRUE or 1 is specified in the Range_lookup argument text box, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. (The Range_lookup argument text box can also be left blank).

The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value.
Vlookup Sample Data
 

Applies To: MS Excel 2010
This tip will be based on the screen shot above

  1. Select cell D3
  2. Select the Formulas tab-Lookup and Reference-Vlookup
  3. Select as below

Vlookup Function Agruement

4. The original Table_array is G2:H8. Dollar signs have been added to the data range to make it absolute-hence we have $G$2:$H$8

5. Select OK and copy the formula down

6. To calculate the Total Sales:
a. Select cell E3 and enter the formula: =(A3*C3)-(D3*(A3*C3))
b. Copy the formula down
7. The final result will be as below

Vlookup Results
 
Provide by our friends at Sage Alchemex
By Tim Hambsch



Rss-Logo Subscribe in RSS
Email
 Subscribe by Email


Feeds
CBA Blog
EXCEL TIPS & TRICKS
Latest Posts
Accounting Excel Tip: Formatting Spreadsheets ..
How to Extract a List of Named Ranges in Excel..
How to Remove Blank Cells in Your Excel Spreadsheet..
How To Easily Hide Zero Values in Excel..
Remove Hyperlinks from an Excel Worksheet..

28
Posts by Date
 2019
September (1)
•Accounting Excel Tip: Formatting Spreadsheets
August (1)
•How to Extract a List of Named Ranges in Excel

 2017
September (1)
•How to Remove Blank Cells in Your Excel Spreadsheet
January (1)
•How To Easily Hide Zero Values in Excel

 2016
November (2)
•Remove Hyperlinks from an Excel Worksheet
•Use the Excel MROUND Function to Round Off Time Values
September (1)
•How to Rearrange Data Table Columns in Excel
May (2)
•How to Calculate a Deposit or Down Payment in Excel
•Easily Generate PivotTables on Separate Excel Worksheets
April (2)
•Learn Some New Features In Excel 2013
•Create an Excel Combination Chart From Scratch
March (1)
•Learn How to Use the Goal Seek Feature
February (1)
•How to Calculate Multiple Results in Excel by Using a Data Table
January (1)
•How to Create a Waterfall Chart

 2015
December (1)
•Excel Tip: How to Change Upper/Lower Case Text in Excel
November (3)
•Common Microsoft Excel Errors and How to Fix Them
•Link an Excel Worksheet Name to a Cell Using the MID and FIND Functions
•Round off Time Values in Excel Using the MROUND function
October (1)
•Minimize Data Entry Errors by Utilizing the Calendar Drop Down List
September (2)
•How to Remove Stubborn Spaces and Characters in an Excel Cell
•FREE e-book: Excel PivotTables and PivotCharts
August (1)
•Excel Tip: Creating First and Last Name Columns from a Full Name Field
June (1)
•Excel Tip: Editing the Same Cell in Multiple Worksheets
April (1)
•Using the TEXT Formula for Sage Intelligence Reporting
February (1)
•Calculating the Frequency of Specific Values Within a Range of Values
January (1)
•FREE WEBINAR: EXCEL TIPS FOR BETTER BUSINESS REPORTS

 2014
August (1)
•DATEDIF and TODAY Functions Combined
June (1)
•Excel Tips: Conditional Formatting… Finding What You Need in a Pile of Data

 2013
August (1)
•Using Slicers in Excel 2010
May (1)
•Using the TRANSPOSE Function in Excel
January (1)
•Using the DSUM Function to Add Based on Multiple Criteria

 2012
October (1)
•VLOOKUP with the Approximate Match
September (1)
•Sharing Slicers Between PivotTables in Excel 2010
August (1)
•Using a Data Entry Form in Excel
July (1)
•How to use the SUBTOTAL Command
May (1)
•How to use the SUMIFS function in Excel
April (1)
•How to Display Filtered Pivot Table Data on Separate Worksheets
Tag Cloud
  • Sage 50 intelligence training and excel
  • Sage 50 excel tips
  • Excel 2013
  • Sage 50 reporting
  • Peachtree reports
  • Name formula columns in excel
  • Format spacing in excel
  • Using excel for sage 50 reporting
  • Calculate in excel
  • Move excel data table
  • Excel combination chart
  • Sage business intelligence
  • Excel goal seek
  • Calculate days in excel
  • Excel waterfall chart
  • Remove spacing in excel
  • Excel 2013 and 2016 tips
  • Pivottables and pivotcharts
  • Change text in excel
  • Editing text in excel worksheets



Share
RSS feed
Atom feed
Bookmark
Google
Delicious
Digg
MySpace
Facebook
Diigo
Yahoo
Stumble
Reddit
My Aol
LinkedIn
Bookmark and Share
  Powered by WorldWide Merchant


© 2023 Computerized Business blog all rights reserved.
CBA Green Bar

  • Home
  • Products
  • Services
  • Resources
  • About
  • Contact
  • Sage 50 2023
  • Sage 50 2022
  • VersAccounts ERP
  • Checks & Forms
  • Sage 50 Support
  • Peachtree Support
  • Tech Support
  • Sage 50 & Peachtree Training
  • Management Consulting
  • Bookkeeping
  • Merchant Services
Computerized Business Associates
 
1009 Northwood Drive
Galesburg IL 61401
480-945-4455 or 800-339-8224
info@computerizedbusiness.com
Sign Up For Our Newsletter!

CBA Peachtree Newsletter Signup Image
Get tips and news from Sage 50 (Peachtree) experts in your inbox
Master Certified Sage Consultants
Arizona, Nevada, California, Nationwide, Worldwide

This website is owned by Computerized Business Associates. Computerized Business Associates is independent from Sage and is not authorized to make any statement, representation, or warranties or grant any license or permission on behalf of Sage regarding any product, service, or website content. Certain materials made available on or through this website are owned by Sage and cannot be used without the prior written permission of Sage.
© 2023 Computerized Business all rights reserved.