finding a set word from a number of words in a cell

On sheet 2 in cell C5 I need it to find it from sheet 1 referring it from the word "Drinks 1" thats in Q1 and so on along the row

In R7 of Sheet1 enter formula:

=COUNTIF(Q$7:Q7,"*"&Sheet2!$A$2&"*")

and copy it down the column

In S7 of Sheet1 enter formula:

=MAX(R7:R10)

in C5 of Sheet2 enter formula:

=IF(COLUMNS($A$1:A$1)>Sheet1!$S$7,"",INDEX(Sheet1!$B$7:$B$10,MATCH(COLUMNS($A$1:A$1),Sheet1!$R$7:$R$10,0)))

and copy it across the columns...

Adjust ranges to suit.

not sure what the "update value sheet" means... have you changed the sheet names in the formula to coincide with your actual sheetnames?

The A1 part is just used as a counter COLUMN($A$1:A$1) = 1, as you drag it it becomes COLUMNS($A$1:B$1) which equals 2 and so on....

Hi
got it working
I found the problem with the update value sheet
i had a - between 2 words of the name of sheet

but i can't get Drinks1 to come up twice when its in the Q7 cell twice
as sometime the item can come a a few times maybe from 1 to 6 times in same cell Q7
can someone please help me with the last bit

am still having a little trouble when the same word comes up and it doesn't pick it up
i have now added the file again

Post a sample file illustrating problem at hand - ie actual vs desired results.

Not entirely sure I understand but perhaps...

Sheet1!R7:
=$R6+(SUMPRODUCT(LEN($Q6)-LEN(SUBSTITUTE($Q6,Sheet2!$A$2,"")))/LEN(Sheet2!$A$2))
copied down to R10

Sheet1!S7:
=MAX(R7:R10)+SIGN(R10)
(S8:S10 can be cleared)

Then with Sheet1 adjusted per the above, in terms of results:

Sheet2!C5:
=IF(COLUMNS($C5:C5)>Sheet1!$S$7,"",INDEX(Sheet1!$B$7:$B$10,MATCH(COLUMNS($C5:C5)-1,Sheet1!$R$7:$R$10)))
copied across to G5

Is that what you wanted ?


Pending value of Sheet2!A2 and the way in which the multiple items are listed in Col Q on Sheet1 that the SUMPRODUCT is open to error