Lookup/match

[chef]

I have never used index/match and dont know if that is even answer

I have a large sheet with colmns from colun C to column AE and rows 12 to 3000

I have a unique reference in cell E26 for example and say AC15..i then have various columns populated

The outcome for ref AC15 is in cells T26 to cells AE26
That would be ok for a vlookup if only one outcome, but I can have an outcome for AC15 in cells T26 to cells AE26 but another sub outcome in cells T57 to cells AE57

There is only one reference but can be 2 or 3 different recorded outcomes

What I am trying to do is on a separate sheet have Reference AC15 display all outcome information releating to that reference and pick up and display all the information from the relevant lines and in tbis case
outcome information from cells T26 to AE26 and outcome from cells T57 to AE57

I hope i have communicated this well enough to make sense and would appreciate any help at all
regards

 

[Special-K99]

So E26 contains a value
Column AC should be searched for the value in E26 but there may be multiple values in column AC.
For each matching value in column AC the values from columns T to AE should be output.
Yep?

Try this, note array formulas so this may be slow, this allows for 1000 rows to be searched, adjust as necessary

in Sheet1!E26 put the value you want to search

in Sheet2!A1
=IFERROR(INDEX(Sheet1!$T$1:$AE$1000,SMALL(IF((Sheet1!E26=Sheet1!$AC$1:$AC$1000),ROW($AC$1:$AC$1000)),ROW(A1))-(ROW(A$1)-1),COLUMN()-19),"")
Array formula, use Ctrl-Shift-Enter
copy across to column L
copy these formulas down to row 1000

 

[chef]

Thanks very much for your help...this was a test and might need applied to 14500 rows and columns A to AF..

Appreciate the help but as you stated was going to be slow, would probable frustrate users and I think i'll let them just put a filter on.

appreaciate your help and would never have been able to come up with that.

have a good day