Match data from 3 columns to return data from the correct 4th column

klwilliams13wf

New Member
Joined
Mar 31, 2021
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
Hi there! I'm trying to have a cell auto-populate the data in a cell based on the data entered in 3 other cells. I've pasted a copy of the spreadsheet I'm working with below. I want to manually enter a number in C4, and in E4, and then pick a number from a drop down box in G4, and have it auto-populate the corresponding lbs/sq ft in N4 based on a chart on another worksheet. I've pasted a copy of the chart below as well. Is this possible??

1617212084818.png
1617212310848.png
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,789
Office Version
  1. 365
  2. 2010
Welcome to Mr. Excel
Would you please provide a few examples, including the results you expect from your examples?
Also, it would help those trying to help you if you use XL2BB so that we don't have to reenter your data.

In your example, do you want an answer of 11.513...?
 
Last edited:

klwilliams13wf

New Member
Joined
Mar 31, 2021
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
Welcome to Mr. Excel
Would you please provide a few examples, including the results you expect from your examples?
Also, it would help those trying to help you if you use XL2BB so that we don't have to reenter your data.

In your example, do you want an answer of 11.513...?
Oh, I'm sorry!! I didn't post a full list of data in my table! Ok, so let's change the thickness to 3/8 or .375; what I would like returned in N4 would be 17.27.

I'm sorry for sounding ignorant, but what is XL2BB?
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,789
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

Does this accomplish it?
Code:
=SUM((Q2:Q8=E2)*(R2:R8=C2)*(S2:S8=G2)*T2:T8)
 

klwilliams13wf

New Member
Joined
Mar 31, 2021
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
oh, I see it!!
Does this accomplish it?
Code:
=SUM((Q2:Q8=E2)*(R2:R8=C2)*(S2:S8=G2)*T2:T8)
No... I want it to look up the WT/ft in the chart, based on what I enter in the depth, width and thickness of the chart. so if I enter 5/8 in x 5/8 in x 1/16 in thick, it will enter .442 as the wt/ft
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,789
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

Book1
BCDEFGHIJKLMNOPQRST
1QTYWIDTHUNITDEPTHUNITTHICKUNITLENGTHUNITWELDEXTUNITLBS/UNITDWTHICKWT/FT
2100.625in0.625in0.0625IN10IN1000.442110.121.437
3110.0750.945
45/85/80.06250.442
53/43/40.0480.457
63/43/40.060.552
71 1/21 1/20.0831.602
8440.37517.27
Sheet1
Cell Formulas
RangeFormula
C2,Q4:R4,E2C2=5/8
N2N2=SUM((Q2:Q8=E2)*(R2:R8=C2)*(S2:S8=G2)*T2:T8)
Q5:Q6Q5=3/4


Of course, you'll have to change the reference to where the table is located.
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,789
Office Version
  1. 365
  2. 2010
Book1
BCDEFGHIJKLMNOPQRST
1QTYWIDTHUNITDEPTHUNITTHICKUNITLENGTHUNITWELDEXTUNITLBS/UNITDWTHICKWT/FT
2101 1/2in1 1/2in0.083IN10IN1001.602110.121.437
3440.37517.27110.0750.945
43/43/40.060.5525/85/80.06250.442
53/43/40.0480.457
63/43/40.060.552
71 1/21 1/20.0831.602
8440.37517.27
Sheet1
Cell Formulas
RangeFormula
N2:N4N2=SUM(($Q$2:$Q$8=E2)*($R$2:$R$8=C2)*($S$2:$S$8=G2)*$T$2:$T$8)
Q4:R4R4=5/8
Q5:Q6Q5=3/4
 

klwilliams13wf

New Member
Joined
Mar 31, 2021
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
Book1
BCDEFGHIJKLMNOPQRST
1QTYWIDTHUNITDEPTHUNITTHICKUNITLENGTHUNITWELDEXTUNITLBS/UNITDWTHICKWT/FT
2101 1/2in1 1/2in0.083IN10IN1001.602110.121.437
3440.37517.27110.0750.945
43/43/40.060.5525/85/80.06250.442
53/43/40.0480.457
63/43/40.060.552
71 1/21 1/20.0831.602
8440.37517.27
Sheet1
Cell Formulas
RangeFormula
N2:N4N2=SUM(($Q$2:$Q$8=E2)*($R$2:$R$8=C2)*($S$2:$S$8=G2)*$T$2:$T$8)
Q4:R4R4=5/8
Q5:Q6Q5=3/4
maybe I didn't enter the formula right, but I'm getting a value error... the table that has the data I want to pull from is on another tab "Lists". Would that be causing the problem. I'm just lost! I copied the formula =SUM(($Q$2:$Q$8=E2)*($R$2:$R$8=C2)*($S$2:$S$8=G2)*$T$2:$T$8) and pasted it into the cell that I want to have the wt/ft populate. Is that right?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,021
Messages
5,639,586
Members
417,100
Latest member
Simon123456789

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