Return list of Multiple Value from differents Cells in the same Column

[carrjonn]

Hi, Everyone.

i've been looking a formulo for so long but found nothing, i really appreciate your help.

i'm trying to make a lists indexing a column that contain labor numbers and each labor contain multiple Materials, the formula work for the first Labor Selected but no continue to the next cell

The array formula is

{=IFERROR(INDEX($A$2:$B$19,SMALL(IF($A$2:$B$19=$I$2,ROW($A$2:$B$19)-1),ROW(A1)),2),"")}

thanks to every

sorry if this was solved on another Thread.... i make some search but not found.

[Bernie Deitrick]

Your error was this:

$A$2:$B$19=$I$2

Checking two columns instead of one...


Array-Enter this instead:

=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$19=$I$2,ROW($A$2:$A$19)),ROW(A1))),"")

[carrjonn]

Thanks is really a better formula... will replace for sure...

but the big issue is about continue to next Cell $A$2:$B$19=$I$3 and then the other $A$2:$B$19=$I$4 to make a list of all materials needed...

[Bernie Deitrick]

The next formulas should also only check one column:

$A$2:$A$19=$I$3

and then the other


$A$2:$A$19=$I$4

[carrjonn]

this can be automatic with a formula???
this table is a simple sheet (created to request this help) from a workbook and the formula make references to others sheets with a lot of data and will be used by various employes.

the column I from I2 to I6 will be constantly changing.

is there a way to make the formula to look From I2 to I6 and return all value for each labor selected in this column

thanks again and sorry to bother you

[Bernie Deitrick]

Try this file....

[carrjonn]

great!!!!! Cheers!!! very helpfull...

a lot of work to do just to adjust it to my 74 cells on my workbook...

thanks!!!

[Bernie Deitrick]

What do you mean 74 cells? In column A/B? Just use Find/replace find 19 and replace with 74 or 1000 or....

If you change your output, then you can use far simpler formulas: