array formula for vertical and horizontal data

[freud1]

Hi Guys,

Think this one is better explained with the example sheet (attached)

I need 2 formulae (actually I think it is the same formula just slightly different):

Formula 1:
Return multiple values from the top row of a sheet (Item1, Item2) if there is an 'x' in the table and the vertical data column (data1, data 2) matches the value on row 5 on sheet 2.

Formula 2:
Return multiple values from the vertical data column (data1, data 2) if there is an 'x' in the table and the values from the top row of a sheet (Item1, Item2) matches the value on row 5 on sheet 2.

I filled in a populated example of what i want returned on sheet 2 - the blue value on row 5 is user input, the cells below are what I need the formulae to return.

I have been up all night trying to get this working so any help would be much appreciated!!

F

[daddylonglegs]

Try these array formulas, respectively in C7 and F7

=IFERROR(INDEX(Sheet1!B$2:B$700,SMALL(IF(INDEX(Sheet1!G$2:CN$700,0,MATCH(C$5,Sheet1!G$1:CN$1,0))="x",ROW(Sheet1!G$2:G$700)-ROW(Sheet1!G$2)+1),ROWS(C$7:C7))),"")

and

=IFERROR(INDEX(Sheet1!G$1:CN$1,SMALL(IF(INDEX(Sheet1!G$2:CN$700,MATCH(F$5,Sheet1!B$2:B$700,0),0)="x",COLUMN(Sheet1!G$1:CN$1)-COLUMN(Sheet1!G$1)+1),ROWS(F$7:F7))),"")

confirmed with CTRL+SHIFT+ENTER and copied down as far as required and further - when valid entries run out you get blanks

change C5 and F5 values to see how results change

[freud1]

Thanks daddylonglegs!

works exactly like I was hoping!!

Also I learnt a thing or two from your formulae!

Thanks again!

F