Formula to Search entire column for a specific word and copy a different cells value.

[mike rand]

Hello all,

Im trying to come up with ta formula

formula is in cell aa6 on sheet called BOM

i would like it to search entire column T on sheet called MATERIAL DATA to find the word GORE. when it finds that word i want it to copy the contents in column G in the same row where the word GORE was match

then list these findings from CEllaa6 in BOM down to AA50 in same.

thanks for your help

mike rand

[63falcondude]

Try this in AA6:

=IFERROR(INDEX('MATERIAL DATA'!G:G,SMALL(IF('MATERIAL DATA'!T$2:T$1000="GORE",ROW('MATERIAL DATA'!T$2:T$1000)),ROWS($1:1))),"") Ctrl Shift Enter

Adjust the ranges to fit your data. Drag the formula down as far as needed.

[63falcondude]

The formula in post #2 assumes that there are multiple rows that have "GORE" in column T and that you want to return all of the corresponding values in column G.

[mike rand]

yes that assumtion is correct

can yoiu tell me what this part of the formula does



ROWS($1:1))),"") Ctrl Shift Enter



thanks

mike

[mike rand]

HEY falcondude,

how do i get this fomula to show a blank cell instead of a zero

=SUMIFS('MATERIAL DATA'!$R$5:$R$1994,'MATERIAL DATA'!$G$5:$G$1994,BOM!$Y5)

thanks

mike

[63falcondude]

can yoiu tell me what this part of the formula does

ROWS($1:1))),"")

Notice that the ROWS function is in the k part of the SMALL function. ROWS($1:1) returns 1 which will return the 1st smallest number.
As you drag the formula down, that will change to ROWS($1:2) which returns 2 and will therefore return the 2nd smallest number etc.

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

[63falcondude]

how do i get this fomula to show a blank cell instead of a zero

=SUMIFS('MATERIAL DATA'!$R$5:$R$1994,'MATERIAL DATA'!$G$5:$G$1994,BOM!$Y5)

Here's one option:
=IFERROR(1/(1/SUMIFS('MATERIAL DATA'!$R$5:$R$1994,'MATERIAL DATA'!$G$5:$G$1994,BOM!$Y5)),"")

[mike rand]

FALCONDUDE,

WORKS PERFECT THANKS.

one more problem i am having i cant seem to figure it out.

this is my formula in cell

=IFERROR(INDEX('MATERIAL DATA'!$G$5:$G$1994,SMALL(IF('MATERIAL DATA'!$T$5:$T$1994="GORE",ROW('MATERIAL DATA'!$T$5:$T$1994)),ROWS($1:1))),"")

i want to modify the formula and drag it down to cells in column.

this is how i modified it

=IFERROR(INDEX('MATERIAL DATA'!$G$5:$G$1994,SMALL(IF('MATERIAL DATA'!$T$5:$T$1994="PQ1,PQ2,PQ3,PQ4,PQ5,PQ6,PQ7,PQ8,PQ9,PQ10,PQ11",ROW('MATERIAL DATA'!$T$5:$T$1994)),ROWS($1:1))),"")

you can see the first formula matches the word "GORE and it works perfect and does exactally what i need

in the second formula you can see now i would like it to match multiple things and when it finds any of them in that column to do the same as the first formula
however i am not sure if ive made ythe formula correctly because it is not doing what the firat formula done.
the second formula works if only search for 1 thing like if i replace the word GORE with PQ1 it works perfect but i need to find all the PQ in the list and copy the data


thanks

mike rand

[63falcondude]

Try this:

=IFERROR(INDEX('MATERIAL DATA'!G:G,SMALL(IF((LEFT('MATERIAL DATA'!T$5:T$1994,2)="PQ")*(MID('MATERIAL DATA'!T$5:T$1994,3,2)+0<=11),ROW('MATERIAL DATA'!T$5:T$1994)),ROWS($1:1))),"") Ctrl Shift Enter