dellehurley
Board Regular
- Joined
- Sep 26, 2009
- Messages
- 173
- Office Version
- 365
- Platform
- Windows
Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><o
></o
>
I used the video <o
></o
>
"Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table" to create a great lookup devise. <o
></o
>
<o
></o
>
I would like to set up another search devise which will look for a word within a list of data and return the results as per above.<o
></o
>
<o
></o
>
I know my problem is with counting the number of times the word is in the list. On the original search I used =SUMPRODUCT(--(Sheet1!I2:I1166=D11),--(Sheet1!J2:J1166=D12),--(Sheet1!<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com
ffice:smarttags" /><st1
lace w:st="on">K2</st1
lace>:K1166=D13)) and it worked.
So I tried SUMPRODUCT(--(Sheet1!I2:I1166="*"&D11&"*") and the wildcard section of the equation works however it returns 0.
I have also tried {=SUM(IF(Sheet1!$G$2:$G$1166=D11,1,0))} this worked successfully to count if I enter the exact text from a cell however I want to look up one word to do a keyword search!
Can anyone help me? <o
></o
>
Thanks<o
></o
>
delle




I used the video <o


"Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table" to create a great lookup devise. <o


<o


I would like to set up another search devise which will look for a word within a list of data and return the results as per above.<o


<o


I know my problem is with counting the number of times the word is in the list. On the original search I used =SUMPRODUCT(--(Sheet1!I2:I1166=D11),--(Sheet1!J2:J1166=D12),--(Sheet1!<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com



So I tried SUMPRODUCT(--(Sheet1!I2:I1166="*"&D11&"*") and the wildcard section of the equation works however it returns 0.
I have also tried {=SUM(IF(Sheet1!$G$2:$G$1166=D11,1,0))} this worked successfully to count if I enter the exact text from a cell however I want to look up one word to do a keyword search!
Can anyone help me? <o


Thanks<o


delle
