# How do I lookup from more than 1 list?

#### mistersteve

##### Board Regular
I hope someone can help
I have a list as below

 Account Product Price UOM Discount Adam Apple

<tbody>
</tbody>

along side there are a few tables

 Account Adam Product Price UOM Discount apple 20 100 50 banana 25 100 40 pear 5 1 Account Mary Product Price UOM Discount banana 3 1 grape 30 100 25 orange 25 100 Account Peter Product Price UOM Discount apple 25 100 80 grape 3 1 30 orange 20 100

<tbody>
</tbody>

after inputting the account and product in my top list I would like a lookup that matches the account then finds the product.
this would then fill in the price, UOM (unit of measure), and discount.

thanking you in advance
Steve

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### Canapone

##### Active Member
Hi,

you could assign a name (Adam, Mary, Peter) to the three ranges

Then it might be something like

=VLOOKUP(B2,INDIRECT(A2),2,0)

B2 houses "apple"

There are for sure other better strategies depending on layout of your data or on the number of ranges you need to manage

Example: if the three ranges are in the same sheet -one below the other- maybe a combination of INDEX/OFFSETT could do the trick.

#### mistersteve

##### Board Regular
Thanks Canapone, works brilliant.

a bit more help if anyone doesn't mind

I have another table, I have named it "list"

So after the look of the account and product, I wish to ignore the account and just look up the product.

 Product Price UOM Discount apple 88 100 50 banana 77 20 80 pear 66 10 50 grape 55 1 25 orange 44 100 10 plum 33 100 50
<colgroup><col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <col width="64" style="width: 48pt;" span="2"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <tbody> </tbody>

I was trying to something like =VLOOKUP(\$B3,INDIRECT(\$A3),2,FALSE)=VLOOKUP(\$B3,List,2,FALSE)
but I got lost
can anyone help please?

#### Canapone

##### Active Member
Hi,

do you need to check if prices of B3 are aligned in the two tables?

Regards

#### chicagocomputerclasses

##### Well-known Member
<table cellpadding="4px" border="1" cellspacing="1">
<tr><td bgcolor="#DFE3E8"></td><td align="center" width="81.6" bgcolor="#DFE3E8">C</td><td width="76.8" align="center" bgcolor="#DFE3E8">D</td><td width="76.8" align="center" bgcolor="#DFE3E8">E</td><td width="76.8" align="center" bgcolor="#DFE3E8">F</td><td width="76.8" align="center" bgcolor="#DFE3E8">G</td><td align="center" width="938.4" bgcolor="#DFE3E8">H</td></tr>
<tr><td bgcolor="#DFE3E8">1</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF">Account</td><td bgcolor="#FFFFFF">Product</td><td bgcolor="#FFFFFF">Price</td></tr>
<tr><td bgcolor="#DFE3E8">2</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF">Peter</td><td bgcolor="#FFFFFF">plum</td><td bgcolor="#FFFFFF">=IFERROR(VLOOKUP(G2,CHOOSE(MATCH(F2,{"Adam";"Mary";"Peter"},0),\$A\$8:\$D\$10,\$A\$14:\$D\$16,\$A\$20:\$D\$22),2,0),VLOOKUP(G2,\$A\$26:\$D\$31,2,0))</td></tr>
<tr><td bgcolor="#DFE3E8">3</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">4</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">5</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">6</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">7</td><td bgcolor="#FFFFFF">UOM</td><td bgcolor="#FFFFFF">Discount</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">8</td><td bgcolor="#FFFFFF">100</td><td bgcolor="#FFFFFF">50</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">9</td><td bgcolor="#FFFFFF">100</td><td bgcolor="#FFFFFF">40</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">10</td><td bgcolor="#FFFFFF">1</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">11</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">12</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">13</td><td bgcolor="#FFFFFF">UOM</td><td bgcolor="#FFFFFF">Discount</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">14</td><td bgcolor="#FFFFFF">1</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">15</td><td bgcolor="#FFFFFF">100</td><td bgcolor="#FFFFFF">25</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">16</td><td bgcolor="#FFFFFF">100</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">17</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">18</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">19</td><td bgcolor="#FFFFFF">UOM</td><td bgcolor="#FFFFFF">Discount</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">20</td><td bgcolor="#FFFFFF">100</td><td bgcolor="#FFFFFF">80</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">21</td><td bgcolor="#FFFFFF">1</td><td bgcolor="#FFFFFF">30</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">22</td><td bgcolor="#FFFFFF">100</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">23</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">24</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">25</td><td bgcolor="#FFFFFF">UOM</td><td bgcolor="#FFFFFF">Discount</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">26</td><td bgcolor="#FFFFFF">100</td><td bgcolor="#FFFFFF">50</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">27</td><td bgcolor="#FFFFFF">20</td><td bgcolor="#FFFFFF">80</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">28</td><td bgcolor="#FFFFFF">10</td><td bgcolor="#FFFFFF">50</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">29</td><td bgcolor="#FFFFFF">1</td><td bgcolor="#FFFFFF">25</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">30</td><td bgcolor="#FFFFFF">100</td><td bgcolor="#FFFFFF">10</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td bgcolor="#DFE3E8">31</td><td bgcolor="#FFFFFF">100</td><td bgcolor="#FFFFFF">50</td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td><td bgcolor="#FFFFFF"></td></tr>
<tr><td colspan="7"></td></tr>
<tr><td colspan="7" bgcolor="#D7E7F9">Sheet1</td></tr>
</table>

#### chicagocomputerclasses

##### Well-known Member
Code:
``=IFERROR(VLOOKUP(G2,CHOOSE(MATCH(F2,{"Adam";"Mary";"Peter"},0),\$A\$8:\$D\$10,\$A\$14:\$D\$16,\$A\$20:\$D\$22),2,0),VLOOKUP(G2,\$A\$26:\$D\$31,2,0))``

This method does not require named ranges. You can still use them if you want.

Replies
2
Views
166
Replies
26
Views
697
Replies
0
Views
488
Replies
0
Views
618
Replies
1
Views
690

### Forum statistics

1,191,485
Messages
5,986,861
Members
440,055
Latest member
CraigTriesHisBest

### 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?

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