Excel lookup table assistance needed

[JenniferR]

I am looking for assistance on using the lookup table functions. I hav
scoured the web and tried the Excel help files to no avail. I have
file that I have created a main dropdown list named component. I the
have a dependent dropdown lists (Product, Region) associated with th
components. My problem arises when I need the due date cell popultae
with the correct due date from the tables. In the example below
would like to see 2 entered automatically in the due date cell for th
apple entry and 1 for the carrot entry. Any help you may provide woul
be GREATLY appreciated.

Master File:
Component | Product | Region | Due Date
Fruit | Apple | West | ???
Veggies | Carrots | Southwest | ???

Tables:
Fruit | West | Midwest | Southwest | East
Apple | 2 | 1 | 3 | 4
Orange | 2 | 4| 3 | 1
Peach | 4 | 2 | 1 | 3

Veggies | West | Midwest | Southwest | East
Carrots | 4 | 2 | 1 | 3
Brocolli | 1 | 3| 4 | 2
Beans | 2 | 1 | 3 | 4

Thanks again,
Jennifer

 

[englishtwit]

am looking for assistance on using the lookup table functions. I hav
scoured the web and tried the Excel help files to no avail. I have
file that I have created a main dropdown list named component. I the
have a dependent dropdown lists (Product, Region) associated with th
components. My problem arises when I need the due date cell popultae
with the correct due date from the tables. In the example below I woul
like to see 2 entered automatically in the due date cell for the appl
entry and 1 for the carrot entry. Any help you may provide would b
GREATLY appreciated.

Master File:
Component | Product | Region | Due Date
Fruit | Apple | West | ???
Veggies | Carrots | Southwest | ???

Tables:
Fruit | West | Midwest | Southwest | East
Apple | 2 | 1 | 3 | 4
Orange | 2 | 4| 3 | 1
Peach | 4 | 2 | 1 | 3

Veggies | West | Midwest | Southwest | East
Carrots | 4 | 2 | 1 | 3
Brocolli | 1 | 3| 4 | 2
Beans | 2 | 1 | 3 | 4

Thanks again,
JenniferR

Jennifer

I hope I understand you correctly.

I think this is your answer.

In the cell where your question marks are, you need a vlookup.

It will say
=Vlookup(B2,'tables'!A:E,2)
This means:
Take the value of the cell B2 - in this case it will be apples, an
look up in tables cols A:E for the same word. If you find it, put th
contents of column 2 of tables into the cell. (The cell is where th
vlookup function is).

Cheers

E

 

[JulieD]

Hi Jennifer

might be an easier way but this seems to work -
take the following sample spreadsheet

|A|B|C|D|E|F|G|H|I|J|K
1 |Fruit|West|Midwest|Southwest|East| |Veggies | West | Midwest | Southwest
| East
2 |Apple| 2 | 1 | 3 | 4| |Carrots | 4 | 2 | 1 | 3
3 |Orange| 2 | 4| 3 | 1| |Brocolli | 1 | 3| 4 | 2
4 | Peach| 4 | 2 | 1 | 3| |Beans | 2 | 1 | 3 | 4
5
6
7 |Component | Product | Region | Due Date
8 |Fruit | Apple | West | ???
9| Veggies | Carrots | Southwest | ???

try the following in D8 & fill down
=IF(A8=$A$1,OFFSET($A$1,MATCH(B8,$A$2:$A$4,0),MATCH(C8,$B$1:$E$1,0)),OFFSET(
$G$1,MATCH(B8,$G$2:$G$4,0),MATCH(C8,$H$1:$K$1,0)))

Hope this helps

Cheers
JulieD