Help on lookup formulae

[leo73pk]

I received a template wherein a formula is given as follows;

IFERROR(LOOKUP(2,1/(C5:CC5<>""),C5:CC5),"0")

I would like to know the interpretation of this lookup formula as i'm unable to understand what is meant by Lookup(2, 1/ part of the formula.

[benishiryo]

hey there. one useful trick to learn when understanding formulas is to press the F9 key to calculate. So go to the cell where the formula resides and click inside the formula bar. select the individual parts of the formula which you don't understand and press F9 to calculate.

select this part and press F9 to calculate:
(C5:CC5<>"")
let's assume the data is blank, non-blank, non-blank, blank, etc. it will show as:
{FALSE,TRUE,TRUE,FALSE,and so on....

the formula then uses 1 divided by a bunch of TRUEs and FALSEs. TRUE is recognized as 1 in Excel and FALSE as 0. so 1 / TRUE is 1 and 1 / FALSE is a DIV error. so:
{#DIV/0!,1,1,#DIV/0!,and so on...

my lookup_value is 2, so since the LOOKUP cannot find an exact match, it returns the last number found; the 2nd non-blank. using C5:CC5 in the result_vector, it will give the value of the 2nd non-blank cell.

hope that helps

[leo73pk]

Thanks for your reply. Is there any other formula to perform the same task?

[leelnich]

Here's an array formula that does the same thing. Paste it in the formula bar and then press CTRL+SHIFT+ENTER to confirm array status.

Please Login or Register  to view this content.

[leo73pk]

Thanks for your response Leelnich. your proposed formula works.

I'm enclosing the actual file for better understanding

In this spreadsheet I have following information

Range C3:AR12
Range C15:AR23
Range C26:AR35
Range C38:AR46

My requirement is to review Country wise Last order quantity, order number and order date. For that I have applied formulas in table F52:H58 which are giving me the desired results

Kindly suggest me more efficient way i.e. in terms of data layout and formulas to achieve the desired results, other than pivot table.

[leo73pk]

Let me reiterate my requirement as follows;

My requirement is to review Last order quantity, order number and order date placed by each Country. For that I have data table with requisite details in enclosed sheet.

[leo73pk]

Here's an array formula that does the same thing. Paste it in the formula bar and then press CTRL+SHIFT+ENTER to confirm array status.

Please Login or Register  to view this content.

Thanks Leelnich. The proposed formula solve the issue.

[leelnich]

Thank you for the rep! Happy to help, though I personally prefer some version of your original LOOKUP() formula because it usually* doesn't require CSE/array entry (even experienced programmers forget to press C+S+E occasionally ). Of course, neither would this:

Please Login or Register  to view this content.

...since MATCH() can also find the largest number less than or equal to lookup_value while ignoring #DIV/0! errors. However, it requires another "trick" - wrapping the lookup_array in an extra INDEX() function - to avoid CSE.

*NOTE: Lookup_arrays generated with IF() statements - as in my post #4 solution - ALWAYS require CSE! I suppose I could have worked around that with:

Please Login or Register  to view this content.

...,but this is long, awkward, AND obscure. I usually try to strike a balance between clarity of function and calculational efficiency.