2 inputs to give range of returns

[Sordini]

Any tips on how to tackle this one would be apprecaited.
Kind regards
Sordini

[ChemistB]

In C6, entered as an array (use CNTRL SHFT ENTER instead of a simple ENTER)
=IF(ROW(A1)>$B$4,"",INDEX(Sheet1!$C$2:$C$6,SMALL( IF(ISNUMBER(SEARCH(Sheet2!$B$2,Sheet1!$C$2:$C$6)),ROW(Sheet1!$C$2:$C$6)-1,10000),ROW(A1)))), dragged down

In B6
=IFERROR(INDEX(Sheet1!$B$2:$B$6,MATCH(C6,Sheet1!$C$2:$C$6,0)),"")
Does that work for you?

[Sordini]

Very clever that works great.
I will have to lookinto the CNTRL SHFT ENTER thing a lot more!
In this example when you do CNTRL SHFT ENTER does mean it is a one time set up? I don't have to do this everytime I open the file?

In either case I am really greatful and thanks a lot

[ChemistB]

True, you do not have to set it up again each time you open the file. What it does is tells Excel that you are working with an array and to assign memory so that Excel can set up values within that array. If you have a lot of calculations and large arrays, it can slow down your spreadsheet calculations so always use judiciously.

[Sordini]

Could you please explain the "-1" and "10000" part in the if statement?
I am having a little trouble applying to a bigger file.

[ChemistB]

Okay
=IF(ROW(A1)>$B$4,"",INDEX(Sheet1!$C$2:$C$6,SMALL( IF(ISNUMBER(SEARCH(Sheet2!$B$2,Sheet1!$C$2:$C$6)),ROW(Sheet1!$C$2:$C$6)-1,10000),ROW(A1))))
Working from the inside out,

IF(ISNUMBER(SEARCH(Sheet2!$B$2,Sheet1!$C$2:$C$6)),ROW(Sheet1!$C$2:$C$6)-1,10000)

What this does, is supply an array of numbers to be used by the SMALL function in the next step (this step is why we need to create the formula as an array)
For C2 through C6, it looks for (in your example " ZA" in each of these cells. If it find it, it'll return a number, if not, it returns an error

Then ISNUMBER converts these to True or False
So in your example, we get {False, True, True, False, False}. Excel is also evaluating the second half of that formula ROW(C2:C6)-1 which evaluates to { 2-1, 3-1,4-1, 5-1, 6-1} or {1,2,3,4,5}

Now we have
IF({False, True, True, False, False}, {1,2,3,4,5}, 10,000) So a False gives us 10000 and a true will be matched up to the corresponding number (or row in the series)
={10000,2,3,10000,10000}

So the answer to your first question, "why the -1" is to get a series that starts with 1. If the your data started in row 12, you would have a "-11"

Next step
SMALL( IF(ISNUMBER(SEARCH(Sheet2!$B$2,Sheet1!$C$2:$C$6)),ROW(Sheet1!$C$2:$C$6)-1,10000),ROW(A1)))
substituting what we just got
SMALL({10000,2,3,10000,10000}, ROW(A1)) becomes SMALL({10000,2,3,10000,10000}, 1) meaning find the 1st smallest value in that array. As you copy this down, you'll have ROW(A2), ROW(A3) or 2nd smallest and 3rd smallest

So Excel returns "2" (Q2, we use 10,0000 to get large numbers for all the values that don't match your search criteria. If your rows are in the 10s of thousands, you'd need to pick a bigger number)

And now we're left with the simple equation
= INDEX(Sheet1!$C$2:$C$6,2) which returns the value in C3
Make sense?

[Sordini]

Really great explanation!
Thanks a lot. It would havee taken me ages to figure out the -1 was related to that! The bigger number was a little easier :-)
Thanks so much for taking the time
Kind regards
Sordini