Formula to make cell equal a value based on another cell

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
443
Office Version
  1. 2019
Platform
  1. Windows
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%

B1C1D1E1F1G1H1I1J1K1L1M1N1O1P1
Agg Gradations
MaterialMartin Marietta #57Dolese #67Dolese 5/8 ChipsHanson 5/8 ChipsC RockD RockDolese 3/8 ChipsMartin Marietta MansandHanson ScreeningsSand1/2" RapMillings1 1/2" RapC33Martin Marietta 5/8Martin 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%
42%6%16%5%4%45%41%81%86%100%54%56%84%98%
81%3%4%2%2%14%6%44%59%100%39%43%67%78%
101%3%3%2%2%11%4%38%54%99%36%40%63%71%
161%3%2%2%2%6%2%23%40%98%29%39%52%49%
301%3%2%2%2%4%2%14%30%89%23%28%42%26%
400%2%2%2%2%3%2%11%26%77%19%25%37%16%
500%2%2%2%2%3%1%8%23%59%15%21%30%9%
800%2%2%2%2%2%1%6%18%28%10%14%20%4%
1000%2%2%1%2%2%1%5%17%18%8%11%17%3%
2000.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%

B1C1D1E1F1G1H1
Superpave Template
Materials#67#673/8 CHIPSHanson 5/8 Chips3/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#67ScrnsHanson 5/8 Chips3/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%
46%6%41.0%5%86%6%
83%3%6.0%2%59%3%
163%3%2.0%2%40%3%
303%3%2.0%2%30%3%
502%2%1.0%2%23%2%
1002%2%1.0%1%17%2%
2001.6%1.6%1.6%1.0%11.1%1.6%
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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))
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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?

Disable AdBlock

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
Back
Top