How do I lookup from more than 1 list?

[mistersteve]

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

 

[Canapone]

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"
A2 houses "Adam"

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]

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]

Hi,

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

Regards

 

[chicagocomputerclasses]

<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]

=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.