Solved

# Reciprocal as an argument in Excel functions

Posted on 2014-09-01
279 Views
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
0

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.
0

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

=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

## Featured Post

### Suggested Solutions

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.