Reciprocal as an argument in Excel functions

Formula in Excel to display the date for the last occurrence of a value in a table

Hello,

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

        =LOOKUP(2,1/(B1:B12=B15),A1:A12)

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

        1/(B1:B12=B15)

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?

Thanks
WeThotUWasAToadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
barry houdiniCommented:
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

=LOOKUP(2,1/(B1:B12=B15),A1:A12)

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

=LOOKUP(9.99E+307,A:A)

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

{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE}

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

{#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!}

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

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.