+ Reply to Thread
Results 1 to 4 of 4

using index match to compare numbers on different sheets based on a common criteria

  1. #1
    Registered User
    Join Date
    08-16-2016
    Location
    Manchester, England
    MS-Off Ver
    Office 2013
    Posts
    41

    using index match to compare numbers on different sheets based on a common criteria

    I have two different sheets with records of sales and I want to use index match to calculate the difference between the numbers on each sheet. I have a vlookup formula which sometimes works but has a few value errors so thought index match would work more efficiently.

    Another part of the sheet also has to dates which it would be handy if differences could be identified. returning blank if the same or the date from the first list if different. This would obviously be a different formula to keep as simple as possible as the results will be in different columns
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    06-02-2012
    Location
    London, England
    MS-Off Ver
    365
    Posts
    397

    Re: using index match to compare numbers on different sheets based on a common criteria

    Could you show us what your expected outcome is?

    Beth.

  3. #3
    Registered User
    Join Date
    08-16-2016
    Location
    Manchester, England
    MS-Off Ver
    Office 2013
    Posts
    41

    Re: using index match to compare numbers on different sheets based on a common criteria

    I have updated my sheet, but essentially i am looking at
    5
    10
    11
    -11

    for the numbers formula and

    blank
    05-Jan
    blank
    14-Jan

    for the dates
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,556

    Re: using index match to compare numbers on different sheets based on a common criteria

    I have placed the data on different sheets Month A and Month B.
    The formulas are placed on the Month B sheet.
    The formula for the difference is: =D5-INDEX('Month A'!D$5:D$8,MATCH(C5,'Month A'!C$5:C$8,0))
    The formula for the date is: =IF(E5=INDEX('Month A'!E$5:E$8,MATCH(C5,'Month A'!C$5:C$8,0)),"",INDEX('Month A'!E$5:E$8,MATCH(C5,'Month A'!C$5:C$8,0)))
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Compare two sheets...index match formula issue?
    By gimlay in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-02-2019, 07:27 PM
  2. [SOLVED] Index First Three Results That Match Criteria In Google Sheets
    By swordswinger710 in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 03-27-2019, 05:06 PM
  3. Using index match based on two match criteria in different columns
    By LearningByError in forum Excel General
    Replies: 2
    Last Post: 08-02-2018, 01:57 PM
  4. [SOLVED] INDEX MATCH Multiple Criteria across two sheets will not work
    By DHT_Tech in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-25-2018, 10:11 AM
  5. VBA Code for Index/Match/Match to input into excel based on 2 criteria inside form
    By Carl Fisher in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2017, 12:11 PM
  6. [SOLVED] Index Match using two sheets for Criteria to Match
    By Locust in forum Excel General
    Replies: 3
    Last Post: 12-01-2013, 12:45 PM
  7. Compare 2 sheets based on criteria
    By khalid79m in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-26-2007, 04:53 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1