# Look Up & Return a Value

#### Kathryn Warren

##### New Member
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>

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Kathryn -

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

Excel 2010
ABCDEFG
1Column 1Machine #CavityVolumeCalculated data
2Item #12415660000.26
3Item #2242520000.28
4Item #3241034520.31
5Item #4251034520.3
6Item #5261034520.29
7Item #6449396000.26
8Item #746500000.34
9Item #8482520000.26
10Item #9561890000.27
11Item #10627830000.26
12Item #1164620000.28
13
14Machine-CavityMachineCavity50,000100,000250,000500,000
152-4240.340.310.280.26
162-5250.340.30.270.26
172-6260.340.290.270.26
182-8280.340.290.260.25
192-102100.280.270.250.24
204-4440.340.310.280.26
214-6460.340.30.270.26
224-8480.340.290.260.25
235-6560.280.270.250.24
246-2620.340.30.270.26
256-4640.280.270.250.24

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
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!

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:
Rich (BB code):
``````=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))
``````

Replies
4
Views
219
Replies
8
Views
197
Replies
6
Views
257
Replies
2
Views
196
Replies
2
Views
121

1,203,465
Messages
6,055,574
Members
444,799
Latest member
CraigCrowhurst

### 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?

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