Lookup Vlue and return all matching rows in order

Hello,

You can try the Array formula in the following attached file.

I am pretty sure they still work even if you have a hundred of repeated lines, because they are looking for k, count the number, and giving back as many as they can in order, IF you have more lines with formula in Lookup Sheet that's it

For the formula, this one is the very first one in Code (Cell A5 in Lookup Sheet)

Please Login or Register  to view this content.

I'm using INDEX with SMALL combo to find the value accordingly. This is how it works:

First, SMALL acts as the part of differentiating between each "results", its job is to return the smallest row in order that there's a "k". What I mean in order is this, in your Input worksheet, k can be found in row 6, 8, 10, 12 and 14, then the first result should be row 6, second result is row 8, and so on.
The syntax of SMALL is SMALL(array, k) whereas Array is the array of number to find, and k is the nth_position (position from the smallest to return, like 1st smallest, 2nd smallest, etc). By using IF with Array, such as IF('Input'A5:A15="k",row(A5:S15)-row(A5)+1), it will return an array of row number for SMALL function to dig in (Find "k", if found, return row number of that row, minus row of first row, plus 1, and the result should be an array like this {2, 4, 6, 8}) Basically that IF is the array part of SMALL. The final part of SMALL, the k number, is utilizing the function of ROW()

Second, my idea is to make so the first line of the results will be SMALL(Look up k here, 1st smallest row), the 2nd line will be SMALL(Look up k here, 2nd smallest row), and I want that number to update itself, so I make it
ROW()-ROW($A$4)
What this does is, making the row number of the formula subtract the row of A4 (which is 4 obviously), and return it back to SMALL as the k number. Do the math with me, row 5 formula will have 5 - 4 = 1, row 6 formula will have 6 - 4 = 2, and so on.

Finally, The reason why it's not actually the row of where it's found, it has to be subtracted by first row of results then plus 1 is that, the Array part of Index does not starts at the row no.1 - it starts at the first line of results also.
INDEX starts it job from row 5 onward, and SMALL have to return the number of row in THAT array (so 5th row in the worksheet will be 1st row in INDEX term, 6th will be 2nd, and so on).
By the way, syntax of INDEX I used is INDEX(array, row_number) (The column_number can be omitted)
And so, putting everything together, we have something like this
INDEX( what to return , SMALL( IF(Array to find k, row of that - first row + 1) , no. current row - header row )
The IFERROR in front of it is to avoid a lot of #NUM! error line when SMALL can't find the 9th smallest row where you can only find 8 rows with k.

Please find the attached files with some tweak in the results and data, proving that this still work with multiple repeated rows.
Mark any row in the Input worksheet with k, and notice the change in the Lookup worksheet as well.

Let me know if you have any further concern.

I see, so you want it to automatically sort itself, see the attached file then. Notice that I used a slightly different approach to get the results sorted itself.

Change the flag in any order, and check the Lookup worksheet.

And yes you are right, I was actually abuse the SMALL function with number of rows, resulting in a sorting order of rows (whichever rows is flagged first got pulled in first).

So in the attached files, I pull the data into code column by using the same method, and in value order (smaller code gets pulled first). Other column is simple INDEX MATCH to match value with the corresponding data.

For god sake, I sorted it by the Code column ... lol ...
Please check the attached file. I'm sorry for misreading it ...

There are a few tiny mistakes you did, but don't worry, with a lot of columns and rows and worksheets, this bounds to happen to most of us.

First, because these are all Array formula, when you enter it, you have to hold Ctrl-Shift and hit Enter. If you've done it right, the formula will be wrapped inside a { }.

Second, when you copy paste a formula into a new worksheet, or changing how your data layout, you should change the reference within the formula accordingly, for example, here is your formula

Please Login or Register  to view this content.

and here is the one in the attached sample file (you can find it at the end of this post)

Please Login or Register  to view this content.

Notice the differences? You did change the range of where to lookup for k, so you should change it for the value in return too (in this case, is the Saving List column right?). They should match each other. (worksheet matching - both in the same worksheet, range matching - both from 5 to 350)

That is the core column to lookup for others value. Now to the rest, it should be almost the same.
This is the formula you have in column D, looking for corresponding value in the Us Column

Please Login or Register  to view this content.

and this is the working one in the sample

Please Login or Register  to view this content.

The first part of INDEX is the column you want to look for it (You can have INDEX lookup your entire table, and give it the row / column number, but then that will be extra work to find the corresponding column number, right?). In the table, the column you want to lookup is G, so change it accordingly.

For the checking part, notice in your formula you are looking "k" and Saving in column A and G. However, flag "k" is in column A, and the Saving is in column H not G, so you should change it to
IF("k"&$E3=Analysis!$A$5:$A$350&Analysis!$H$5:$H$350

For the ROW() part, the reference within it should match the table of data you are looking for also. Notice that it does not have to be in a particular worksheet or column, so you can point it into a column the worksheet where you have the formula (the less link between worksheet the better in my opinion), but the number of starting and ending row must be the same as your lookup reference (For example, you are checking row 5 to 350 in "Analysis" worksheet, so you can appoint A5 to A350, or X5 to X350, ... as long as they are also from row 5 to row 350), then the second ROW should have the first row of the data range (From 5 to 350, it will be $A$5, $Y$5 or even AB$5$, if you change to 10 to 350, it will be $A$10, or $R$10, etc)

Everything else is in place in my opinion, remember to match the column with the corresponding column you want it to return value ("Code" in Column B in Analysis worksheet, while "Product Description" is in column C, and so on).

In short, make sure these following things are happening:
- Array formula, so all formula is wrapped inside a { } (Using Ctrl-Shift-Enter to enter the formula)
- Reference must match (same worksheet, same row number, apply to all reference in formula)
- If results aren't what you were expecting, check if you pointed it to the right column (INDEX Reference)

And here is your sample with working formula.
Don't hesitate to let me know if you have any other problems.

Glad I could help.

Don't hesitate to let me know if you have any other question.

Have a great day.