+ Reply to Thread
Results 1 to 4 of 4

index match with approximate and exact lookups

  1. #1
    Registered User
    Join Date
    04-18-2014
    Location
    Maryland
    MS-Off Ver
    2013
    Posts
    32

    index match with approximate and exact lookups

    I have two databases with client information that I need to merge. There's about 20,000 records so it's way too much to do manually.

    Sheet 1 has the client records and their unique identifying number, along with the date of their order. Clients may appear more than once.

    Sheet 2 has additional client information such as age and associated department.

    I've created helper columns on both sheets that concatenate the ID and date to create a string: ex ID: 12345678, date number is 42466, so helper column is 1234567842466

    Generally, index match works well for this, matching based on the helper columns and pulling in the info I need. However, some records have incorrectly differing dates ex: sheet 1 has recorded a client on 4/6/16, but sheet two says that same client was actually on 4/4/16, even though the dates should match. It's a bit of a mess, but as far as my data integrity goes, it's acceptable.

    What I would like to do is find some way to do an index match or similar with the exact ID, and the exact or closest date. Is this possible?

    Thanks!

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: index match with approximate and exact lookups

    Would you please attach a sample workbook with non-sensitive data. Thanks.

    BTW: I normally throw in a delimiter when I concatenate cells so that they are easier to read like 12345678:42466.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: index match with approximate and exact lookups

    Small world, somebody else asked this question earlier today and you can see the solution I proposed.

    Basically, you use the MIN(IF) array to find the difference between the target date and the closest date, and then use that to modify the input date for an exact match.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  4. #4
    Registered User
    Join Date
    04-18-2014
    Location
    Maryland
    MS-Off Ver
    2013
    Posts
    32

    Re: index match with approximate and exact lookups

    Ben, Thanks very much, that works!

+ 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] Match Index to an approximate value
    By bturner73 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-20-2016, 03:28 PM
  2. [SOLVED] vba to replace string from exact match list by searching approximate value
    By johnlara in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-18-2015, 10:12 AM
  3. Exact Match Lookups Slowing Down Calculations
    By majormorgan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-17-2015, 11:24 AM
  4. [SOLVED] index match - 2 criteria, one approximate
    By abhishek007 in forum Excel General
    Replies: 11
    Last Post: 04-11-2015, 05:46 PM
  5. Index(Match) with 2 criteria, one of which is approximate
    By mikeronni in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-12-2014, 01:38 PM
  6. [SOLVED] INDEX & MATCH for approximate matches or combine VLOOKUP & HLOOKUP
    By michaljireht in forum Excel General
    Replies: 4
    Last Post: 12-01-2014, 08:41 PM
  7. [SOLVED] Lookups-an exact match is not found
    By Mike O'Donnell, Columbia MD in forum Excel General
    Replies: 1
    Last Post: 05-13-2005, 10:06 PM

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