Vlookup or not

[Don Fardie]

Trying to create a spreadsheet where the user can pick a Machine from one cell, then pick the Material to run in the machine from another cell and based on those 2 inputs the cost per Kg is shown in another cell. The idea is to use drop down menus for the selections. Is that possible to use Data Validation Lists in a VLOOKUP formula or should I be doing it another way. Not trying to over think this, but kind of a newbie to VLOOKUP formulas!!
Thanks in advance.........Don

 

[Eric W]

You could do something like this:

	A	B	C	D	E	F	G	H	I	J
1	Machine	Material	Cost		Machine	Material		Machine	Material	Cost
2	Two	Plastic	7		One	Tin		One	Tin	1
3					Two	Paper		One	Paper	2
4					Three	Plastic		One	Plastic	3
5						Glass		One	Glass	4
6								Two	Tin	5
7								Two	Paper	6
8								Two	Plastic	7
9								Two	Glass	8
10								Three	Tin	9
11								Three	Paper	10
12								Three	Plastic	11
13								Three	Glass	12
14

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Array Formulas Cell Formula C2 {=INDEX(J2:J13,MATCH(A2&"|"&B2,H2:H13&"|"&I2:I13,0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

You have a list of your machines in E, your materials in F, and then a combined list with costs in H:J. Then in A2 you can put a drop-down pointing to E2:E4, in B2 you have a drop-down pointing to F2:F5, then you can put the formula in C2 to look up the cost.

This should give you some ideas.

 

[Don Fardie]

You could do something like this:

	A	B	C	D	E	F	G	H	I	J
1	Machine	Material	Cost		Machine	Material		Machine	Material	Cost
2	Two	Plastic	7		One	Tin		One	Tin	1
3					Two	Paper		One	Paper	2
4					Three	Plastic		One	Plastic	3
5						Glass		One	Glass	4
6								Two	Tin	5
7								Two	Paper	6
8								Two	Plastic	7
9								Two	Glass	8
10								Three	Tin	9
11								Three	Paper	10
12								Three	Plastic	11
13								Three	Glass	12
14

<tbody>
</tbody>

Sheet7​

Array Formulas Cell Formula C2 {=INDEX(J2:J13,MATCH(A2&"|"&B2,H2:H13&"|"&I2:I13,0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

You have a list of your machines in E, your materials in F, and then a combined list with costs in H:J. Then in A2 you can put a drop-down pointing to E2:E4, in B2 you have a drop-down pointing to F2:F5, then you can put the formula in C2 to look up the cost.

This should give you some ideas.

Thanks Eric....this is what I'm looking for. I'll give it a try!!

 

[Don Fardie]

Thanks Eric...It worked Great!!!

 

[Eric W]

Glad to help!