+ Reply to Thread
Results 1 to 3 of 3

vlookup or index match with multiple conditions (Date closest match) in pivot table

  1. #1
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    960

    vlookup or index match with multiple conditions (Date closest match) in pivot table

    Hello Excel Experts,

    I have a pivot table and i am trying to pull cell values based on multiple criteria, and the data i am trying to pull is from a pivot table.

    The matching criteria are:
    A Exchange Rate Center - Exact Match Type
    Z Exchange Rate Center - Exact Match Type
    Effective MAF Date - CLOSEST Match (more explanation below)

    The 'Effective MAF Date' is where we had an agreement. So it can be any random date. But the rule is, assuming for a particular Route (Exchange Rate centre from A to Z) has an agreement say in Jan 1, 2010 and in the future, the same Route has an ammendment say in Mar 15, 2013. If a service was required say Aug 8, 2011. The desired result/outcome of MAF and mileage will be taken as per Jan 1, 2010 agreement NOT Mar 15, 2013.

    Given the same agreement dates (Jan 1, 2010 and Mar 15, 2013), if the required service date was AT and/or AFTER Mar 15, 2013, then I would need to have the MAF and mileage that was per Mar 15, 2013 agreement. If it's BEFORE Jan 1, 2010, I would want it to be 'UA'.

    I hope my explanation is clear enough, if not feel free to ask. And thank you in advanced for your help!vlookup or index match with multiple conditions in pivot table.xlsx
    Last edited by dluhut; 12-16-2014 at 03:04 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    960

    Re: vlookup or index match with multiple conditions (Date closest match) in pivot table

    Any Excel Experts willing to take up this challenge?

  3. #3
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: vlookup or index match with multiple conditions (Date closest match) in pivot table

    The lack of response could partly be due to the data setup, that pivottable would be a mess to make formulas from.
    I'm just supplying a crude quickfix here involving some helper columns, see if this is something you can work with.

+ 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] Need help with vlookup/INDEX MATCH in getting a value using 2 different conditions
    By muheebrahman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-30-2013, 06:44 AM
  2. Replies: 6
    Last Post: 04-11-2013, 11:27 AM
  3. Replies: 13
    Last Post: 12-13-2012, 11:44 AM
  4. [SOLVED] Formula selection - Vlookup? Match? Pivot table? Returning multiple values
    By enigmaes in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-21-2012, 12:21 PM
  5. INDEX/MATCH with Multiple Conditions
    By Lost123 in forum Excel General
    Replies: 4
    Last Post: 03-26-2010, 01: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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.05997 seconds
  • Memory Usage 8,939KB
  • Queries Executed 16 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (3)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (3)postbit_legacy
  • (3)postbit_onlinestatus
  • (3)postbit_wrapper
  • (4)showthread_bookmarksite
  • (5)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1