SUM PRODUCT and Return Multiple Items From One Lookup Value for Table

[dellehurley]

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><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-comffice:smarttags" /><st1lace w:st="on">K2</st1lace>: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

 

[Aladin Akyurek]

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><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-comffice:smarttags" /><st1lace w:st="on">K2</st1lace>: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

Try...

=SUMPRODUCT(
    --ISNUMBER(SEARCH(D11,Sheet1!I2:I1166)),
    --(Sheet1!J2:J1166=D12),
    --(Sheet1!<st1:place w:st="on">K2</st1:place>:K1166=D13))

 

[dellehurley]

yipee it worked thank you so much!

 

[dellehurley]

Okay the sum works however I still have an issue with this work sheet.

When I do a search the result shows up for the exact matches however the other partial matches show up as #NUM! as you can see below.

<table x:str="" style="border-collapse: collapse; width: 679pt;" width="903" border="0" cellpadding="0" cellspacing="0"><col style="width: 53pt;" width="70" span="3"> <col style="width: 323pt;" width="430"> <col style="width: 197pt;" width="263"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl24" style="height: 15pt; width: 53pt;" width="70" height="20">Class</td> <td class="xl24" style="border-left: medium none; width: 53pt;" width="70">1st</td> <td class="xl24" style="border-left: medium none; width: 53pt;" width="70">2nd</td> <td class="xl25" style="border-left: medium none; width: 323pt;" width="430">Title</td> <td class="xl25" style="border-left: medium none; width: 197pt;" width="263">Author</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="background: black none repeat scroll 0% 0%; height: 12.75pt; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="17">F</td> <td class="xl26" style="border-left: medium none;">A02</td> <td class="xl26" style="border-left: medium none;"> -</td> <td class="xl26" style="border-left: medium none;">Wombat</td> <td class="xl26" style="border-left: medium none;">Steve Parish</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="background: black none repeat scroll 0% 0%; height: 12.75pt; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" height="17">F</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">A02</td> <td class="xl26" style="border-top: medium none; border-left: medium none;"> -</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Wombat</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Steve Parish</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt;" x:err="#NUM!" height="17">#NUM!</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:err="#NUM!">#NUM!</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:err="#NUM!">#NUM!</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:err="#NUM!">#NUM!</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:err="#NUM!">#NUM!</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt;" x:err="#NUM!" height="17">#NUM!</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:err="#NUM!">#NUM!</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:err="#NUM!">#NUM!</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:err="#NUM!">#NUM!</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:err="#NUM!">#NUM!</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="border-top: medium none; height: 12.75pt;" x:err="#NUM!" height="17">#NUM!</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:err="#NUM!">#NUM!</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:err="#NUM!">#NUM!</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:err="#NUM!">#NUM!</td> <td class="xl26" style="border-top: medium none; border-left: medium none;" x:err="#NUM!">#NUM!</td> </tr> </tbody></table>
The equation I have used for the search is
{=IF(ROWS(A$14:A14)<=$D$9,INDEX(Sheet1!C$2:C$1166,SMALL(IF(Sheet1!$G$2:$G$1166=$D$8,ROW(Sheet1!$G$2:$G$1166)-ROW(Sheet1!$G$2)+1),ROWS(A$14:A14))),"")}

Does anyone know how I would be able to fix this.

Thanks
delle