lookup list

[loner2003]

I am trying to get excel to return a list of all vlookup results when there is more than 1 instead of just giving me an N/A result. Can I do that within vlookup or is there another way? See my attached example spreadsheet.

[TYork]

Would filtering by code 3 ultimately achieve what you are looking for?

[loner2003]

That is really what I want to do, but I want it to occur automatically as a result of a user entry. So if a user inputs a value of 524210 for Code 3 (such as in a registration form where they would be answering questions and completing blank fields manually), I want the results of their entry to filter into a small chart of results that the user will be able to view.

[TYork]

I can't think of an easy way. I would most likely employ some code under this circumstance.

[rwgrietveld]

Normally you would have a construction like

if it was a single (first) lookup. As it is the Nth I would use

Where this is the code

Please Login or Register  to view this content.

[rwgrietveld]

On the Ozgrid they tackle it as such:

Please Login or Register  to view this content.

Your use: =Nth_Occurrence($E$2:$E$16, "524210", 1, 0, -3)

http://www.ozgrid.com/Excel/find-nth.htm

[DonkeyOte]

In this type of setup any formula based approach is always going to be messy and is unlikely to offer the same flexibility of a UDF - this is partly down to the layout of the source data.

Golden rule is to avoid repetitive calculations and that will be adopted in below.

If we assume for sake of argument / demo that using the sample file B21 holds code3 of interest: 524210

First - identify the number of records that should be returned in results table:

Please Login or Register  to view this content.

In this case that will be 2.

Next - identify the row(s) on which the n instances are to be found:

Please Login or Register  to view this content.

(I used 27 to account for possibility of 5 matches - you should adjust as neccessary)

We use A23:A27 to generate the results table (specifically to avoid [expensive] repetitive calcs)

Please Login or Register  to view this content.

Changing B21 to 531120 you should get 5 records returned

[loner2003]

Thanks all. SOLVED.