Formula to make cell equal a value based on another cell

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
416
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

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
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
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))
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top