How do I lookup from more than 1 list?

M

mistersteve

Board Regular
Joined
Aug 18, 2014
Messages
110
Office Version
  1. 365
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
Sort by date Sort by votes
C

Canapone

Active Member
Joined
May 10, 2007
Messages
463
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.
 
Upvote 0
M

mistersteve

Board Regular
Joined
Aug 18, 2014
Messages
110
Office Version
  1. 365
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.

ProductPriceUOMDiscount
apple8810050
banana772080
pear661050
grape55125
orange4410010
plum3310050
<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?
 
Upvote 0
chicagocomputerclasses

chicagocomputerclasses

Well-known Member
Joined
Mar 2, 2015
Messages
645
<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>
 
Upvote 0
chicagocomputerclasses

chicagocomputerclasses

Well-known Member
Joined
Mar 2, 2015
Messages
645
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Do i need a formula or macro to do this?
Replies
2
Views
166
jaugent27
J
C
  • Solved
Generate related lists of products that have similar attributes
2 3
Replies
26
Views
697
camspy
C
M
  • Question
Help with FilterXML formula
Replies
0
Views
488
montex01
M
J
  • Question
VBA to extract row data to sheet and update data set
Replies
0
Views
618
justatroy
J
R
  • Question
I need to examine the relationship between two parameters and allocate points to them.
Replies
1
Views
690
RockandGrohl
R

Forum statistics

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

Share this page

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
Top