Lookups

Originally Posted by rolltyde12

I need to be able to lookup information on a specific sheet and have every instance of a specific word be placed in a column on another sheet.

So, if I have a ton of data on Sheet 1, I want to be able to have a heading on Sheet 2 that says "Ace", and have a function that looks for the word "Ace" on Sheet 1, and places every instance of this word under the heading "Ace" on Sheet 2.

So on Sheet 1:
Ace 34
Blue 32
Ace 99
Green 34

On Sheet 2, you would see:

ACE
Ace 34
Ace 99


any help?

see the attached file.

Starguy, thanks for the response. I am still unsure how the helper column plays into the equation. I realize the function pulls the information that shows "a". Can you go into more detail? When I change to lookup value to b or c, nothing happens.

Originally Posted by rolltyde12

Hi Starguy, I am attaching a file for you to look at. I need the function to be placed under Ace on the Formation Sheet. I want it to lookup every occurance of the letters "Ac" on the Master Sheet in Column B. Whenever a call has those letters, that cell will be added to Column A on the Formation Sheet. Can you help?

col B in Master sheet is helper. it looks in col C for value "Ac" and if cell in col C contains "Ac" in it, it will assign an incremental number to it in col B.

see the file.

Originally Posted by rolltyde12

Starguy, thanks for the response. I am still unsure how the helper column plays into the equation. I realize the function pulls the information that shows "a". Can you go into more detail? When I change to lookup value to b or c, nothing happens.

be sure that calculation is set to Automatic
before changing value
go to Tools > Options > Calculation tab > and check Automatic
then change value and see how it grabs all values.