# Thread: Excel lookups (XP)

1. ## Excel lookups (XP)

I tried searching for this, but no luck...

Here goes:
I'm trying to find the Nth occurrence of an item, rather than the first only. I can do it ok if all items are sorted, but not if they're not.
For example:
<pre>Jones Eggs
Jones Bacon
Jones Toast
Smith Toast
Smith Ham
</pre>

This works fine for me as such. I can get the count of the Jones records or the Smith records, etc. I can also pull out each column B value based on knowing which column A item I'm looking for (If, that is, they are sorted by column A). But, if I add another "Jones" line at the end, I can't get to it's value without first sorting by column A, which I don't want to do given the particular worksheet I'm on. If I could find the Nth occurrence of "Jones", that would work perfectly.

I'm trying to accomplish this WITHOUT using vba, with a formula only. Any ideas?
Thanks,

2. ## Re: Excel lookups (XP)

Assume your "table" is in Cols A and B, starting in row 1.

In D1 enter in the value for the occurence you are interested in
In E1 enter the name you are interested in
In C1 enter the formula:
<pre>=COUNTIF(\$A\$1:A1,\$E\$1)</pre>

and copy C1 down (autofill) thru the rows covered in A/B

In F1 enter:
<pre>=INDEX(B:B,MATCH(D1,C:C,0))</pre>

which is the result in Col B corresponding to the "d1"th occurence of E1 in Column A.

Steve

3. ## Re: Excel lookups (XP)

Here is a way if you do not want the intermediate column calculated (that is no formula in Col C)
D1 and E1 are still the occurence number and the name in Col a respectively
In a cell add the ARRAY formula (confirm with ctrl-shift-enter):
<pre>=INDEX(\$B\$1:\$B\$100,SMALL(IF(\$A\$1:\$A\$100=\$E\$1, ROW(\$A\$1:\$A\$100)),\$D\$1))</pre>

Expand the range as desired

Steve

4. ## Re: Excel lookups (XP)

Thanks so much for the quick answer! I used the first example, with some changes of course (the real problem is never quite as simple as the sample used to describe the problem...). I used Offset instead of Index, only because it was easier, and the range to search wasn't always known.

One final question: Is there any way to conditionally set the height of rows, without VBA? Since I allocated a set number of rows to each section whether they were all needed or not, It would be great if I could shrink the blank rows on sections which had fewer data items.

Thanks again,

5. ## Re: Excel lookups (XP)

Manually or VB are the only means of changing the height. It can not be done via formulas

Steve

6. ## Re: Excel lookups (XP)

On second thought, this method (using SMALL) is MUCH better, requiring fewer (and shorter) formulas. Mostly I used the INDEX function after all, as it seems simpler, with fewer arguments, but sometimes had to use the OFFSET function, because I needed to return a range of cells. My raw data looks like:
<pre>Person1 Project1 DataItem DataItem ....
Person2 Project2 DataItem DataItem ....
Person3 Project1 DataItem DataItem ....
etc.
etc.
</pre>

I added a list of people and a list of projects on another sheet for reference, then created a presentation sheet with the formulas we've been discussing. The result ends up looking like:
<pre>Person1
Project1 DataItem DataItem ...
Project2 DataItem DataItem ...
etc.
etc.
Person2
Project1 DataItem DataItem ...
Project2 DataItem DataItem ...
etc.
etc.

etc.
</pre>

The only problem is I had to allocate a certain number of rows for each person, so some people have more blank space below their project details than others. That's why I asked about conditionally shrinking or hiding rows.

With slight modifications, I should be able to make another sheet which is Project-centric rather than Person-centric, but using the same raw data area.
Thanks again for all your help!

7. ## Re: Excel lookups (XP)

Could you use a pivot table to get results that you want?

Or perhaps just a macro routine to "create" your output rather than formulas

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•