INDEX, MATCH and Return an Array?? Or something like that...

Hi!

I'm working with raw data which is split into 'trays' of 96 values (12 columns, 8 rows - See Input Tab). Each tray is separated by some text and blank cells.

The end user will be able to use a dropdown column next to the raw data to mark each tray with a label ("Tray 1 Read 1", etc). The output tab will then be programmed to display trays in an order that I specify (Tray 1 read 1, tray 1 read 2, etc). The output order never changes but the input order can be completely random.

I need a formula which is capable of searching for the label "Tray 1 read 1" (can be dynamic or cell ref) in the blue label column and displaying the relevant tray of 96 values as shown in the output tab, bearing in mind that the "tray 1 read 1" tray could be anywhere in the dataset. I was thinking using INDEX/MATCH could do it but I get confused very quickly with this sort of thing. This also must return any blank cells as blank cells and not 0. Nothing set in stone yet so open to any ideas.

Thanks in advance

Phoebe

Microtitre plates???


=IFERROR(TAKE(FILTER(1/(1/Input!C4:N71),SCAN("",Input!O4:O71,LAMBDA(x,y,IF(y="",x,y)))=B12),8),"")

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[Hi Glenn!

There's one more thing I want to add... a cell next to the output table that tells the user what the tray in question "tray 1 read 1" was originally labelled in the data (on the left where it says Test tray: 1 etc)... if that makes sense?

Cheers,
P]

Actually, scratch that... I've hit an issue with the original formula. Sometimes there are blank cells in the raw data or values ending in * which need to be processed slightly differently. I am performing a calculation on the data in the output table, based on whether F8 (or any cell in that table) is blank, contains a * or contains a numerical value. =IF(ISBLANK(F8),"Missing",(IF(ISNUMBER(SEARCH("~*",F8)),"MARKED",(((top secret maths calculation/irrelevant)))). However when I populate the output table using the method you kindly provided before, this formula doesn't behave the way I need it to. Is there another way to identify blank cells and *'s in the output table as per the previous solution. Or can the previous solution be modified to allow for this?

Many thanks

Phoebe

I do not understand. Post a sample file showing the input you have and the output you want to see. Use the original sample file??

A guess (if not I REALLY do need to SEE what you mean).

=LET(a,IFERROR(TAKE(FILTER(Input!C4:N71,SCAN("",Input!O4:O71,LAMBDA(x,y,IF(y="",x,y)))=B12),8),""),IF(a=0,"Missing",a))