# Formula to make cell equal a value based on another cell

#### jondavis1987

##### Active Member
In a worksheet i have a Name listed in C3. In B9:B19 I have sizes of that material. In C9:C19 i have formulas that equal to a corresponding sheet called Agg Gradations. In Agg Gradations Row 3 there's a bunch of material names. In B4:B17 there's sizes. In columns under the corresponding name there's a value that matches to the size and material. Is there a way to have a formula in each of those cells that will find the correlating values? Let's say C3 says C33 and B4 says 1". I would like the formula to go to Agg Gradations, find the column in row 3 that says C33, finds the size listed in column B and follows that row to where the two meet. In this case C33 in Agg Gradations would would be in Cell P3, 1" appears in B4, so the the value i need in the original worksheet would be P4. In this case it would be 100%

 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1 N1 O1 P1 Agg Gradations Material Martin Marietta #57 Dolese #67 Dolese 5/8 Chips Hanson 5/8 Chips C Rock D Rock Dolese 3/8 Chips Martin Marietta Mansand Hanson Screenings Sand 1/2" Rap Millings 1 1/2" Rap C33 Martin Marietta 5/8 Martin Marietta 3/8 1" 92% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 3/4" 69% 92% 100% 100% 100% 100% 100% 100% 100% 100% 100% 100% 99% 100% 1/2" 32% 52% 96% 77% 63% 100% 100% 100% 100% 100% 94% 87% 99% 100% 3/8" 17% 32% 71% 51% 20% 96% 96% 100% 100% 100% 80% 77% 96% 100% 4 2% 6% 16% 5% 4% 45% 41% 81% 86% 100% 54% 56% 84% 98% 8 1% 3% 4% 2% 2% 14% 6% 44% 59% 100% 39% 43% 67% 78% 10 1% 3% 3% 2% 2% 11% 4% 38% 54% 99% 36% 40% 63% 71% 16 1% 3% 2% 2% 2% 6% 2% 23% 40% 98% 29% 39% 52% 49% 30 1% 3% 2% 2% 2% 4% 2% 14% 30% 89% 23% 28% 42% 26% 40 0% 2% 2% 2% 2% 3% 2% 11% 26% 77% 19% 25% 37% 16% 50 0% 2% 2% 2% 2% 3% 1% 8% 23% 59% 15% 21% 30% 9% 80 0% 2% 2% 2% 2% 2% 1% 6% 18% 28% 10% 14% 20% 4% 100 0% 2% 2% 1% 2% 2% 1% 5% 17% 18% 8% 11% 17% 3% 200 0.3% 1.6% 1.3% 1.0% 1.5% 1.5% 0.9% 3.6% 11.1% 3.2% 4.0% 6.8% 10.9% 1.3%

 B1 C1 D1 E1 F1 G1 H1 Superpave Template Materials #67 #67 3/8 CHIPS Hanson 5/8 Chips 3/8 SCREENING #67 JMF % 17% 25% 23% 10% 25% Plant % 10% 30% 15% 18% 7% 20% New % 10% 30% 15% 18% 7% 20% Materials #67 #67 Scrns Hanson 5/8 Chips 3/8 SCREENING #67 1" 100% 100% 100% 100% 100% 100% 3/4" 92% 92% 100.0% 100% 100% 92% 1/2" 52% 52% 100.0% 77% 100% 52% 3/8" 32% 32% 96.0% 51% 100% 32% 4 6% 6% 41.0% 5% 86% 6% 8 3% 3% 6.0% 2% 59% 3% 16 3% 3% 2.0% 2% 40% 3% 30 3% 3% 2.0% 2% 30% 3% 50 2% 2% 1.0% 2% 23% 2% 100 2% 2% 1.0% 1% 17% 2% 200 1.6% 1.6% 1.6% 1.0% 11.1% 1.6%

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### jondavis1987

##### Active Member
I figured it out. Was complicating it with trying to do vlookup combined with hlookup. This was all i needed. =INDEX('Agg Gradations'!C2:O15,MATCH(B9,'Agg Gradations'!B2:B15,0),MATCH(C3,'Agg Gradations'!C1:O1,0))

Replies
25
Views
383
Replies
5
Views
106
Replies
3
Views
133
Replies
3
Views
143
Replies
4
Views
64

1,130,037
Messages
5,639,678
Members
417,104
Latest member
Nelsini

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

### Which adblocker are you using?

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

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