4

Consider:

    +---------------+
    | Column A      |
+---+---------------+
| 1 | Milan         |
+---+---------------+
| 2 | Paris         |
+---+---------------+
| 3 | London        |
+---+---------------+
| 4 | Milan         |
+---+---------------+
| 5 | Firenza       |
+---+---------------+
| 6 | Napoli        |
+---+---------------+
| 7 | Amsterdam     |
+---+---------------+
| 8 | Copenhagen    |
+---+---------------+
| 9 | Amsterdam     |
+---+---------------+

Suppose I filter Column A to look for cities in Italy. The following results are returned.

    +---------------+
    | Column A      |
+---+---------------+
| 1 | Milan         |
+---+---------------+
| 4 | Milan         |
+---+---------------+
| 5 | Firenza       |
+---+---------------+
| 6 | Napoli        |
+---+---------------+

Now, consider the following expression:

=IFERROR(VLOOKUP($AR5,Copy!$I$1:$K$3,2,0),OFFSET($A$1,2,0))

Does OFFSET refer to the WHOLE of the source data -- in which case my default value in case of error would be London -- or only the result set -- in which case my default value in case of error would be Firenze?

I'm seeing inconsistent results in my worksheet, and what to narrow down my debugging options by undrrstanging what's supposed to happen in this circumtance.

1

3 Answers 3

3

First regarding debugging formulas in general:

The best way to do this is to use the Evaluate formula functionality (in the Formulatab). This gives you a very good way to see what happens in each step/parameter. Also, if you highlight a part in your formula (e.g. the comple OFFSET formula), press F9. This will evaluate the highlighted statement in the formula bar.

The OFFSET will return London, as OFFSET will also consider hidden cells.

If you want to return the second visible cell, you need a small trick involving an extra column: Place the formula =SUBTOTAL(3,A1)+C1 in C2 (assuming that your table starts in row 1 and column C is available. Then copy the formula down. It will now show you a counter for all visible cells, i.e. the numbers will change according to your autofilter.

Now the formula =INDEX(A:A,MATCH(2,C:C,0))will return you the second visible element.

3

I don't believe OFFSET() excludes hidden/filtered data. From the example below, you can see that even when part of the data is hidden/filtered, the formula in C1 points to the same cell.

enter image description here

To get the 3rd visible item from your list, you may use this array formula1:

=INDEX(xmen,
       SMALL(IF(SUBTOTAL(3,OFFSET(xmen,ROW(xmen)-ROW(A2),0,1)),ROW(xmen)),3)-1,1)

Where:

xmen → refers to your data range excluding any headings (A1:A9 in your example, A2:A9 in the sample below)
A2 → points to the first item in your range (non-heading)

For example:

enter image description here

--
1 Must be committed/entered using Ctrl + Shift + Enter after you integrate it with your VLOOKUP formula

0
0

Keep it simple with this:

=VLOOKUP(SUBTOTAL(5,A:A),A:B,2,FALSE)

It finds the smallest number that is visible (subtotal 5 is minimum) and vlookup the value (which also takes the first value found with that index number).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .