# 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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### 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
311
Replies
5
Views
84
Replies
3
Views
115
Replies
3
Views
123
Replies
5
Views
79

1,127,711
Messages
5,626,420
Members
416,183
Latest member
IanA

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