• Products
    • Sage Intelligence Reporting
      • Enrolment
    • Velixo
      • Enrolment
        • Velixo for Acumatica
        • Velixo for Sage Intacct
  • Services
    • Consulting
    • Training
    • Development
  • Support
    • Support Overview
    • Create Support Ticket
    • Book A Session
    • Knowledgebase
  • About
    • About Us
  • Contact
  • Products
    • Sage Intelligence Reporting
      • Enrolment
    • Velixo
      • Enrolment
        • Velixo for Acumatica
        • Velixo for Sage Intacct
  • Services
    • Consulting
    • Training
    • Development
  • Support
    • Support Overview
    • Create Support Ticket
    • Book A Session
    • Knowledgebase
  • About
    • About Us
  • Contact
Quick Support
Book Demo

Using the VLOOKUP formula with the approximate match in Excel

By admin on May 10, 2018April 19, 2019

Have you ever used the VLOOKUP formula with the approximate match in Excel? By using this formula, you’re able to extract the correct trade discount percentage based on the quantity of a specific item purchased. In this tip, we demonstrate how to set this up.

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. 

This tip will be based on the above screenshot:

1. Select cell D3.

2. Select the Formulas tab—Lookup and Reference-Vlookup.

3. Select as below:

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:

                       

This is a quick and easy way of using a VLOOKUP formula to extract the correct trade discount percentage based on the quantity of a specific item purchased.                  

The post Using the VLOOKUP formula with the approximate match in Excel appeared first on Sage Intelligence.

Source: Excel on Steroids

Post navigation

How to create more accurate forecasts with Excel
Save your entire report to PDF using macros and Sage Intelligence

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Why Working In Excel Works
  • Alchemex Welcomes Velixo 6 For MYOB Advanced
  • Welcome to Velixo Reports Version 4.2
  • Part 3 of 3: Learn how to create a Management Pack in Sage 50cloud Intelligence
  • Part 3 of 3: Create and customize reports easily with the Financial Report Designer

Search

Contact Us

Phone: +61 2 9890 2339
Email: sales@alchemex.com.au
Address: PO Box 212, Deaken West, Canberra, Australia, 2600

Products

  • Intelligence Reporting
  • Velixo Reports

Services

  • Consulting
  • Training
  • Development

About

  • About Us
  • Partners
  • Blog

Support

  • New Ticket
  • Community (Coming Soon)
  • Knowledgebase

Phone: +61 2 9890 2339
Sales: sales@alchemex.com.au
Support: support@alchemex.com.au

Facebook-f Linkedin

© All rights reserved Alchemex Pty Ltd 2020

Made with ❤ by Unfazed Ltd