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%
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

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,127,025
Messages
5,622,276
Members
415,890
Latest member
Apopolis

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