Need formula assistance

[abdumon7]

File attached

[nick93]

You've added no information about what you're trying to accomplish here...

[cbatrody]

Hi,

Welcome to the forum.

You would need array formula for this.

Try the following in B11:
=IFERROR(SUBSTITUTE(INDEX($A$4:$O$4,SMALL(IF(RIGHT($A$4:$O$4,3)="[E]",COLUMN($A$5:$O$5)),ROW(A1)))," [E]",""),"")

In C11:
=IFERROR(SUBSTITUTE(INDEX($A$4:$O$4,SMALL(IF(RIGHT($A$4:$O$4,3)="[I]",COLUMN($A$5:$O$5)),ROW(B1)))," [I]",""),"")

both array entered (to be confirmed by pressing CTRL+SHIFT+ENTER)

see the attached file!

[tim201110]

=IFERROR(SUBSTITUTE(INDEX($A$4:$BI$4,SMALL(IF(ISNUMBER(SEARCH("[E]",$A$4:$BI$4)),COLUMN($A$4:$BI$4)),ROWS($A$1:$A1)))," [E]",""),"") as array formula

[abdumon7]

Thanks for your help and do apologize for the wrong example given.

I am using a database so I just mentioned a single line as an example.

How can I use this formula to collect data from the database ,based on given criteria (Column A11)

Revised file attached.

[cbatrody]

Try the following in B11:

=IFERROR(SUBSTITUTE(INDEX(INDEX($A$3:$O$8,MATCH($A$11,$A$3:$A$8,0),1):INDEX($A$3:$O$8,MATCH($A$11,$A$3:$A$8,0),15),SMALL(IF(RIGHT(INDEX($A$3:$O$8,MATCH($A$11,$A$3:$A$8,0),1):INDEX($A$3:$O$8,MATCH($A$11,$A$3:$A$8,0),15),3)="[E]",COLUMN($A4:$O4)),ROW(A1)))," [E]",""),"")

C11:
=IFERROR(SUBSTITUTE(INDEX(INDEX($A$3:$O$8,MATCH($A$11,$A$3:$A$8,0),1):INDEX($A$3:$O$8,MATCH($A$11,$A$3:$A$8,0),15),SMALL(IF(RIGHT(INDEX($A$3:$O$8,MATCH($A$11,$A$3:$A$8,0),1):INDEX($A$3:$O$8,MATCH($A$11,$A$3:$A$8,0),15),3)="[I]",COLUMN($A4:$O4)),ROW(B1)))," [I]",""),"")

both array entered (CTRL+SHIFT+ENTER).

See the attached file.

[abdumon7]

Yes. It's working now.
Thank you verymuch

[cbatrody]

You are welcome

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

[Richard Buttrey]

Only just picked this up but will you both please note forum rule 1 about thread titles. This one is not acceptable.

@ abdumon. Please edit your first post and change the title to something more meaningful so that others who may search the subject later may benefit.