Look Up & Return a Value

[Kathryn Warren]

I want to look up and return a value from a "menu" of items.

Item # 1 is on Machine # 2 w 4 Cavities and a Volume of 1,566,000
Find Machine #2 in Chart
Find # of Cavities in Chart
Go to Volume columns & find the cell that your volume falls between or over
Return Value in the chart

	Machine #	Cavity	Volume
Item #1	2	4	1,566,000	0.26
Item #2	2	4	252,000	0.28
Item #3	2	4	103,452	0.31
Item #4	2	5	103,452	0.3
Item #5	2	6	103,452	0.29
Item #6	4	4	939,600	0.26
Item #7	4	6	50,000	0.34
Item #8	4	8	252,000	0.26
Item #9	5	6	189,000	0.27
Item #10	6	2	783,000	0.26
Item #11	6	4	62,000	0.28

<tbody>
</tbody>

Machine	Cavity	50,000	100,000	250,000	500,000
2	4	0.34	0.31	0.28	0.26
2	5	0.34	0.3	0.27	0.26
2	6	0.34	0.29	0.27	0.26
2	8	0.34	0.29	0.26	0.25
2	10	0.28	0.27	0.25	0.24
4	4	0.34	0.31	0.28	0.26
4	6	0.34	0.3	0.27	0.26
4	8	0.34	0.29	0.26	0.25
5	6	0.28	0.27	0.25	0.24
6	2	0.34	0.3	0.27	0.26
6	4	0.28	0.27	0.25	0.24

<tbody>
</tbody>

 

[adamfitz77]

Kathryn -

I think I see what you want, but you tell me if I have it wrong:

Excel 2010

	A	B	C	D	E	F	G
1	Column 1	Machine #	Cavity	Volume	Calculated data
2	Item #1	2	4	1566000	0.26
3	Item #2	2	4	252000	0.28
4	Item #3	2	4	103452	0.31
5	Item #4	2	5	103452	0.3
6	Item #5	2	6	103452	0.29
7	Item #6	4	4	939600	0.26
8	Item #7	4	6	50000	0.34
9	Item #8	4	8	252000	0.26
10	Item #9	5	6	189000	0.27
11	Item #10	6	2	783000	0.26
12	Item #11	6	4	62000	0.28
13
14	Machine-Cavity	Machine	Cavity	50,000	100,000	250,000	500,000
15	2-4	2	4	0.34	0.31	0.28	0.26
16	2-5	2	5	0.34	0.3	0.27	0.26
17	2-6	2	6	0.34	0.29	0.27	0.26
18	2-8	2	8	0.34	0.29	0.26	0.25
19	2-10	2	10	0.28	0.27	0.25	0.24
20	4-4	4	4	0.34	0.31	0.28	0.26
21	4-6	4	6	0.34	0.3	0.27	0.26
22	4-8	4	8	0.34	0.29	0.26	0.25
23	5-6	5	6	0.28	0.27	0.25	0.24
24	6-2	6	2	0.34	0.3	0.27	0.26
25	6-4	6	4	0.28	0.27	0.25	0.24

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E2 =VLOOKUP(B2&"-"&C2,A$15:G$25,MATCH(D2,$D$14:$G$14,1)+3,0) E3 =VLOOKUP(B3&"-"&C3,A$15:G$25,MATCH(D3,$D$14:$G$14,1)+3,0) E4 =VLOOKUP(B4&"-"&C4,A$15:G$25,MATCH(D4,$D$14:$G$14,1)+3,0) E5 =VLOOKUP(B5&"-"&C5,A$15:G$25,MATCH(D5,$D$14:$G$14,1)+3,0) E6 =VLOOKUP(B6&"-"&C6,A$15:G$25,MATCH(D6,$D$14:$G$14,1)+3,0) E7 =VLOOKUP(B7&"-"&C7,A$15:G$25,MATCH(D7,$D$14:$G$14,1)+3,0) E8 =VLOOKUP(B8&"-"&C8,A$15:G$25,MATCH(D8,$D$14:$G$14,1)+3,0) E9 =VLOOKUP(B9&"-"&C9,A$15:G$25,MATCH(D9,$D$14:$G$14,1)+3,0) E10 =VLOOKUP(B10&"-"&C10,A$15:G$25,MATCH(D10,$D$14:$G$14,1)+3,0) E11 =VLOOKUP(B11&"-"&C11,A$15:G$25,MATCH(D11,$D$14:$G$14,1)+3,0) E12 =VLOOKUP(B12&"-"&C12,A$15:G$25,MATCH(D12,$D$14:$G$14,1)+3,0) A15 =B15&"-"&C15 A16 =B16&"-"&C16 A17 =B17&"-"&C17 A18 =B18&"-"&C18 A19 =B19&"-"&C19 A20 =B20&"-"&C20 A21 =B21&"-"&C21 A22 =B22&"-"&C22 A23 =B23&"-"&C23 A24 =B24&"-"&C24 A25 =B25&"-"&C25 <tbody> </tbody>

<tbody>
</tbody>

A couple of things to note:

(1) VLOOKUP(lookupvalue,lookuparray,column#,option) looks up a value in the first columnof an array (in this case, the first table), and grabs a corresponding value from a specified column on that row (one of the most useful functions in Excel). Note that I added a column before your data to combine Machine # and Cavity # into a value that will be unique within the table. VLOOKUP searches on this combination. The value for column# depends on the volume of the machine/cavity combo, so I use the MATCH function to get the correct column #.

(2) the MATCH(lookupvalue,lookuparray,option) function identifies which column you are grabbing a value from. With option=1 it returns the closest value which is less than or equal to lookupvalue. You must have the numbers in the top row of the second table arranged in ascending order (L to R) for this work. Note that I only performed the match on cols D:G to ensure MATCH is only looking at numbers (just to be safe), and then add three to the result.

Voila!

Adam

 

[Aladin Akyurek]

Sheet1, A:F, the table to seek in (Source)...

Machine	Cavity	50,000	100,000	250,000	500,000
2	4	0.34	0.31	0.28	0.26
2	5	0.34	0.3	0.27	0.26
2	6	0.34	0.29	0.27	0.26
2	8	0.34	0.29	0.26	0.25
2	10	0.28	0.27	0.25	0.24
4	4	0.34	0.31	0.28	0.26
4	6	0.34	0.3	0.27	0.26
4	8	0.34	0.29	0.26	0.25
5	6	0.28	0.27	0.25	0.24
6	2	0.34	0.3	0.27	0.26
6	4	0.28	0.27	0.25	0.24

<tbody>
</tbody>

Sheet2, A:E, where the results must obtain and the look up values...

	Machine #	Cavity	Volume	RESULT
Item #1	2	4	1,566,000	0.26
Item #2	2	4	252,000	0.28
Item #3	2	4	103,452	0.31
Item #4	2	5	103,452	0.3
Item #5	2	6	103,452	0.29
Item #6	4	4	939,600	0.26
Item #7	4	6	50,000	0.34
Item #8	4	8	252,000	0.26
Item #9	5	6	189,000	0.27
Item #10	6	2	783,000	0.26
Item #11	6	4	62,000	0.28

<colgroup><col style="width: 76pt; mso-width-source: userset; mso-width-alt: 3584;" width="101"> <col style="width: 70pt; mso-width-source: userset; mso-width-alt: 3299;" width="93"> <col style="width: 48pt;" width="64"> <col style="width: 74pt; mso-width-source: userset; mso-width-alt: 3527;" width="99"> <col style="width: 95pt; mso-width-source: userset; mso-width-alt: 4494;" width="126"> <tbody>
</tbody>

E2, control+shift+enter, not just enter, and copy down:

=INDEX(Sheet1!$C$2:$F$12,
  MATCH(1,IF(Sheet1!$A$2:$A$12=$B2,
   IF(Sheet1!$B$2:$B$12=$C2,1)),0),
  MATCH($D2,Sheet1!$C$1:$F$1,1))