Reciprocal as an argument in Excel functions

Posted on 2014-09-01
Last Modified: 2014-09-29
Formula in Excel to display the date for the last occurrence of a value in a table


This is a follow-up question to the above thread. The formula given (by Flyster) for the solution was:


My question in this thread relates to the structure of the 2nd term:


I have seen (and I now use) a number of solutions — usually from Barry Houdini — which include a reciprocal argument. (Is that correct terminology, ie for when a function is a denominator)? However, I don't really understand the rationale or mechanism at work and therefore cannot exploit the same approach in other similar situations.

Can someone provide a brief explanation of reciprocal arguments or point me to a resource which explains this form?

Question by:WeThotUWasAToad
    LVL 68

    Expert Comment

    Flyster explained it already. It does only make sense as a whole, as this formula combines several techniques:
    B5 = max(B5:B6)    is 1 if B5 is the max, else 0
    Dividing 1 thru that gives 1 or #Div/0, an invalid value.
    Looking up value 2 in that results leads to accessing the row with the max value.
    LVL 50

    Accepted Solution

    Hello Steve,

    I partially explained the technique in one of your other questions, but it's probably more appropriate here:

    When you use this formula


    It finds the last (positionally) value in B1:B12 that matches B15 and returns the corresponding value from A1:A12.

    Finding the appropriate value in one range and returning a value from another range is normal LOOKUP behaviour but the "trick" here is in how that match is made.

    Note that there is no requirement for any range to be sorted for the formula to work as per my definition in bold. Of course for your scenario you want the latest date, so you need the dates sorted ascending so that the last date (positionally) is also the latest date (chronologically).

    The formula works in a similar way to this formula


    That finds the last number in column A. Again the range doesn't need to be sorted and could also contain error values or text - it still finds the last number in column A. Why?

    For that you need to understand how LOOKUP behaves when it doesn't find the lookup value.

    Because LOOKUP is supposed to work with an ascending range it can use "binary search" - only examining a limited number of values, e.g. it looks at the midpoint to see if the lookup value is > or < than that value, if it's greater it can discard the first 50% of the values and if it's < then it can discard the top 50%. The function keeps on examining the midpoint and discarding the other values - when the lookup value isn't found it's left with only one value - the last number in the range.

    For this version the principle is the same - the lookup array will return only 1s or #DIV/0! errors so if you use 2 as LOOKUP value (2 is just a convention - it could be 20, 200, 2000 or 10000, any number > 1) it matches with the last 1 which corresponds with the last row where B15 matches:

    Specifically if I have "x" in B15 and also have "x" in B4, B7 and B11 then B1:B12=B15 returns this array


    When you divide 1 by that array you get this as the lookup range:


    So the LOOKUP matches with the 1 at position 11 and returns the value in A11

    regards, barry

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now