Excel Magic Trick 1086: 3 Way Lookup Formula & Conditional Formatting, Date Criteria Mismatch




About this tutorial:

Video duration: 10:33
Download Excel File:
Full Title: Excel Magic Trick 1086: 3 Way Lookup Formula & Conditional Formatting, Date Criteria Mismatch & Merged Cell Trouble
See how to:
1. (00:11 min) Create formula to lookup a value from a table with three criteria: INDEX and MATCH and MONTH functions. MONTH and Text Month Name Joined (Ampersand) to the number 1 to get a month value and match a serial number date with month Number Format to match against a text month.
2. (05:24 min) Conditional formatting formula to highlight the intersection based on three…

Post Author: OfficeTutes.com

Apple lover, ICT and LEAN consultant, MS Office lecturer My other website with video tutorials - Tutorials, guides and news for iPhones and iPads

12 thoughts on “Excel Magic Trick 1086: 3 Way Lookup Formula & Conditional Formatting, Date Criteria Mismatch

    Sandeep Goswami

    (August 10, 2017 - 2:18 pm)

    very help full post

    Gary Hutson

    (August 10, 2017 - 2:18 pm)

    Love this Mike. Another excellent video! 🙂

    Chris Mike

    (August 10, 2017 - 2:18 pm)

    i am trying to replicate this exact table but instead of 'Joe, Sue, etc' I need to have ranges "1-5, 6-10, 11-15" 

    Dan Linington

    (August 10, 2017 - 2:18 pm)

    Hi, I have been trying to modify your formula to allow for merged cells that contain text but not a date- is there a way of doing this?

    Sandeep Singh

    (August 10, 2017 - 2:18 pm)

    Thank you for sharing..

    pmsocho

    (August 10, 2017 - 2:18 pm)

    Cool! I would go with OFFSET but you approach is smarter 🙂

    krn14242

    (August 10, 2017 - 2:18 pm)

    Thanks Mike.

    Derek Lowry

    (August 10, 2017 - 2:18 pm)

    brilliant 

    Just thought I'd share my solutions too. the biggest question was how to go from Jan to Feb to March etc 

    conditional format
    AND($A3=$B$10,B$2=$B$9,DATE(2014,MONTH($B$8&1),1)=INDEX($B$1:$AW$1,INT((COLUMNS($B1:B1)-1)/4)*4+1))

    lookup
    INDEX($B$3:$AW$6,MATCH(B10,A3:A6,0),MATCH(B9,B2:E2,0)+(4*(MONTH(B8 & 1)-1)))

    Bill Szysz

    (August 10, 2017 - 2:18 pm)

    Great solution!! I really like it !!! :)))

    James Tays

    (August 10, 2017 - 2:18 pm)

    would this be a bad formula?
    {=INDEX(OFFSET(A3,0,MATCH(B8,TEXT(B1:AW1,"mmmm"),0),4,4),MATCH(B10,A3:A6,0),MATCH(B9,B2:E2,0))}

    ExcelIsFun

    (August 10, 2017 - 2:18 pm)

    Excel Magic Trick 1086: 3 Way Lookup Formula & Conditional Formatting, Date Criteria Mismatch 

Leave a Reply

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