Extract data from a table into separate column

[BRISBANEBOB]

I am trying to extract and list data from a range if a string is found in an adjacent column. A sample workbook is attached.

Any help much appreciated.

[Bosco]

In H30, copied across and down :

=IFERROR(INDEX($H$7:$H$25,AGGREGATE(15,6,ROW($H$7:$H$25)-ROW($H$6)/ISNUMBER(FIND(L$29,TEXT($E$7:$E$25,"mm-dd-yy"))),ROWS($1:1))),"")

Regards
Bosco

[jason.b75]

If Bosco's suggestion is not giving the results thagt you require, then a more detailed explaination of your expected results will be needed.
Bosco's formula will not return the same results as you provided, but it returns those that your explaination implies (with the discrepancies being under the '7' column heading in row 29).

It could be caused by a formatting issue based on regional settings, when I open your sample file, E7 defaults to date format dd/mm/yyyy while the rest of column E retains the appearance of a text format, 0-0-#0.

[BRISBANEBOB]

Thanks, Jason. You are 100% correct - an unfortunate (or careless) selection of sample numbers by me has generated dates in USA but not in Australia!

I've clarified (I hope) the solution sought in the workbook attached.

[jason.b75]

This looks good with your sample, Bob.

=IF(H29="","",IFERROR(INDEX($H$7:$H$25,SMALL(IF(ISNUMBER(SEARCH("-"&H$29&"-",$E$7:$E$25)),ROW($E$7:$E$25)-ROW($E$7)+1),ROWS(H$30:H30))),""))

Array confirmed with Shift Ctrl Enter.

Or, using Bosco's AGGREGATE method (non-array)

=IF(H29="","",IFERROR(INDEX($H$7:$H$25,AGGREGATE(15,6,ROW($E$7:$E$25)-ROW($E$7)+1/ISNUMBER(SEARCH("-"&H$29&"-",$E$7:$E$25)),ROWS(H$30:H30))),""))

Not sure if there are any advantages or disadvantages with either method.

[AlKey]

Try this
Enter in H30 and drag formula across and down

Please Login or Register  to view this content.

Or this

Please Login or Register  to view this content.

Both are regular formulas.

[BRISBANEBOB]

Please can you tell me what part the '15'and '6' play in the formula as in AGGREGATE(15,6?

Thanks

[AlKey]

Please can you tell me what part the '15'and '6' play in the formula as in AGGREGATE(15,6?

Thanks

AGGREGATE() contains number of different functions: 15 is SMALL() and 6 is to ignore errors.

[BRISBANEBOB]

Ah! Something more to learn.

Thanks - worked perfectly

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.