Lookup Multiple criteria in table

nmod

New Member
Joined
Apr 13, 2011
Messages
4
Hello,

I am trying to sum multiple lookups in a table but just cant get it. Here is my table:

1
100000
2
10000
3
1000
4
100
5
10
6
1
7
1000000
D
1111111
<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody> </tbody>

147

So if lookup 147 I want the return to be 11000100.

This is what I was trying =SUM(VLOOKUP(SEARCH(A1:A8,A10),A1:B8,2,))

Thank you in advance.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Hi.

Don't you mean 1100100?

=SUMPRODUCT(SUMIF(A1:A8,MID(A10,ROW(INDIRECT("1:"&LEN(A10))),1),B1:B8))

or, if you insist on using VLOOKUP:

=SUM(VLOOKUP(0+MID(A10,N(INDEX(ROW(INDIRECT("1:"&LEN(A10))),,)),1),A1:B8,2,))


Regards
 
Upvote 0
i am not sure that I understood your problem

but if i am guessing right, then:


<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B10</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">LOOKUP(<font color="Red">--MID(<font color="Green">A10,ROW(<font color="Purple">INDIRECT(<font color="Teal">"1:" & LEN(<font color="#FF00FF">A10</font>)</font>)</font>),1</font>),A1:B7</font>)</font>)</td></tr></tbody></table></td></tr></table><br />



Excel 2013
AB
11100000
2210000
331000
44100
5510
661
771000000
8
9
101471100100
Sheet1







Hello,

I am trying to sum multiple lookups in a table but just cant get it. Here is my table:

1100000
210000
31000
4100
510
61
71000000
D1111111

<tbody>
</tbody>

147

So if lookup 147 I want the return to be 11000100.

This is what I was trying =SUM(VLOOKUP(SEARCH(A1:A8,A10),A1:B8,2,))

Thank you in advance.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top