Vlookup with multiple cells

[WBlenner]

I have a sheet containing work tasks and associated hazards:

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Task1[/TD]
[TD]Hazard 1[/TD]
[TD]Control 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hazard 2[/TD]
[TD]Control 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hazard 3[/TD]
[TD]Control 3[/TD]
[/TR]
[TR]
[TD]Task 2[/TD]
[TD]Hazard 4[/TD]
[TD]Control 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hazard 5[/TD]
[TD]Control 5[/TD]
[/TR]
</TBODY>[/TABLE]

For each task there may be up to 6 separate hazards.

In a new sheet i want to select the Task from a drop-down menu in A1 then using Vlookup, populate B1:B3 with corresponding Hazards, C1:C3 with corresponding controls etc.


I am a novice, i have a little bit of experience with Vlookup, but i wonder if MATCH or INDEX would be better suited?

Any help appreciated!!

 

[mfexcel]

I think what you need is to create a dynamic drop down list with Data Validation
The following site contain very good tutorials about the topic
http://www.contextures.com/excel-data-validation-multiple.html#more

 

[Aladin Akyurek]

Can you change the lay-out as shown below:

[TABLE="width: 194"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3640" width=102><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3271" width=92><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: white"]Task1[/TD]
[TD="class: xl63, width: 102, bgcolor: white"]Hazard 1[/TD]
[TD="class: xl63, width: 92, bgcolor: white"]Control 1[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]Task1[/TD]
[TD="class: xl63, width: 102, bgcolor: white"]Hazard 2[/TD]
[TD="class: xl63, width: 92, bgcolor: white"]Control 2[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]Task1[/TD]
[TD="class: xl63, width: 102, bgcolor: white"]Hazard 3[/TD]
[TD="class: xl63, width: 92, bgcolor: white"]Control 3[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]Task2[/TD]
[TD="class: xl63, width: 102, bgcolor: white"]Hazard 4[/TD]
[TD="class: xl63, width: 92, bgcolor: white"]Control 4[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]Task2[/TD]
[TD="class: xl63, width: 102, bgcolor: white"]Hazard 5[/TD]
[TD="class: xl63, width: 92, bgcolor: white"]Control 5[/TD]
[/TR]
</TBODY>[/TABLE]