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

klwilliams13wf · Mar 31, 2021

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

kweaver · Apr 2, 2021

You MUST reference the other tab.
If you use XL2BB the table won't have to be typed by me (or anyone).

kweaver · Apr 2, 2021

Use this assuming your data is on the sheet named Lists and starting in A1.

Code:

=SUM((Lists!$A$2:$A$8=E2)*(Lists!$B$2:$B$8=C2)*(Lists!$C$2:$C$8=G2)*Lists!$D$2:$D$8)

Change the reference to the 8th row to however many rows are in the table.

klwilliams13wf · Apr 5, 2021

Good morning! I've tried to download XL2BB. I select the file but the add-on isn't being added to my excel program.

I've copied your formula and verified the information, but it is still returning a #VALUE error. So frustrating! I really appreciate your efforts by the way!!!!

Fluff · Apr 5, 2021

Try it like

Excel Formula:

=SUMPRODUCT((Lists!$A$2:$A$8=E2)*(Lists!$B$2:$B$8=C2)*(Lists!$C$2:$C$8=G2)*Lists!$D$2:$D$8)

klwilliams13wf · Apr 5, 2021

I figured out Xl2bb!

NEW TAKEOFF LAYOUTkw.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

1

Description

Grade

QTY

WIDTH

UNIT

DEPTH

UNIT

THICKNESS

UNIT

LENGTH

UNIT

WELD SIZE

EXTENSION

UNIT

LBS PER UNIT

UNIT

TOTAL LBS

$/UNIT

Custom $/Unit

PLATE PROCESSING TOTAL

MATERIAL TOTAL

COST/UNIT

LABOR TOTAL

OVERALL TOTAL

MAN HOURS

2

HSS Square Column

5

4

in

4

in

0.250

in

10

ft

50

ft

4

1/8

6.34

eq chart

HSS SQUARE COLUMN

Cell Formulas
Range	Formula
F2	F2	=IF(D2="","",IF(A2="Weld Simpson Bucket","",IF(A2="Weld Base Plate","",IF(A2="Weld Top Plate","",IF(ISNUMBER(SEARCH("Column",A2)),"in",IF(ISNUMBER(SEARCH("holes",A2)),"dia",IF(ISNUMBER(SEARCH("studs",A2)),"dia",IF(ISNUMBER(SEARCH("bucket",A2)),"ea",IF(ISNUMBER(SEARCH("bolt",A2)),"dia",IF(ISNUMBER(SEARCH("plate",A2)),"in",IF(ISNUMBER(SEARCH("",A2)),"")))))))))))
H2,J2	H2	=F2
N2	N2	=K2*D2

Cells with Data Validation
Cell	Allow	Criteria
X2	List	=Pricing
I2	List	=Wall_Thickness

NEW TAKEOFF LAYOUTkw.xlsx

L

M

N

O

3

1/2

0.048

0.301

4

1/2

0.060

0.362

5

5/8

0.048

0.367

6

5/8

0.060

0.442

7

3/4

0.048

0.457

8

3/4

0.060

0.552

9

3/4

0.075

0.690

10

3/4

0.083

0.753

11

3/4

0.090

0.800

12

3/4

0.120

1.029

13

1

0.048

0.629

14

1

0.060

0.763

15

1

0.075

0.945

16

1

0.083

1.036

17

1

0.090

1.115

18

1

0.120

1.437

19

1 1/4

0.048

0.807

20

1 1/4

0.060

0.981

21

1 1/4

0.075

1.200

22

1 1/4

0.083

1.318

23

1 1/4

0.090

1.443

24

1 1/4

0.120

1.846

25

1 1/4

0.188

2.400

26

1 1/2

0.048

0.956

27

1 1/2

0.060

1.163

28

1 1/2

0.075

1.455

29

1 1/2

0.083

1.602

30

1 1/2

0.090

1.716

31

1 1/2

0.120

2.254

32

1 1/2

0.188

3.230

33

1 1/2

0.250

3.700

34

1 3/4

0.060

1.370

35

1 3/4

0.083

1.884

36

1 3/4

0.090

2.011

37

1 3/4

0.120

2.663

38

2

0.060

1.564

39

2

0.075

1.966

40

2

0.083

2.143

41

2

0.090

2.316

42

2

0.120

3.050

43

2

0.188

4.320

44

2

0.250

5.410

45

2

0.313

6.320

46

2 1/2

0.090

2.979

47

2 1/2

0.120

3.890

48

2 1/2

0.188

5.590

49

2 1/2

0.250

7.110

50

2 1/2

0.313

8.450

51

3

0.120

4.700

52

3

0.188

6.870

53

3

0.250

8.810

54

3

0.313

10.580

55

3

0.375

11.750

56

3 1/2

0.120

5.520

57

3 1/2

0.188

8.150

58

3 1/2

0.250

10.510

59

3 1/2

0.313

12.700

60

3 1/2

0.375

14.710

61

4

0.120

6.340

62

4

0.188

9.420

63

4

0.250

12.210

64

4

0.313

14.830

65

4

0.375

17.270

66

4

0.500

21.630

67

4 1/2

0.120

7.310

68

4 1/2

0.188

10.700

69

4 1/2

0.250

13.910

70

4 1/2

0.313

16.980

71

4 1/2

0.375

19.820

72

5

0.120

7.840

73

5

0.188

11.970

74

5

0.250

15.620

75

5

0.313

19.080

76

5

0.375

22.370

77

5

0.500

28.430

78

5 1/2

0.125

9.010

79

5 1/2

0.165

11.740

80

5 1/2

0.188

13.300

81

5 1/2

0.250

17.320

82

5 1/2

0.313

21.220

83

5 1/2

0.375

24.930

84

5 1/2

0.500

31.840

85

6

0.188

14.530

86

6

0.250

19.020

87

6

0.313

23.340

88

6

0.375

27.480

89

6

0.500

35.240

90

6

0.625

42.300

91

7

0.188

17.080

92

7

0.250

22.420

93

7

0.313

27.590

94

7

0.375

32.580

95

7

0.500

42.050

96

7

0.625

50.760

97

8

0.188

19.630

98

8

0.250

25.820

99

8

0.313

31.840

100

8

0.375

37.690

101

8

0.500

48.850

102

8

0.625

59.320

103

9

0.250

29.230

104

9

0.313

36.100

105

9

0.375

42.790

106

9

0.500

55.660

107

9

0.625

67.820

108

10

0.188

24.750

109

10

0.250

32.630

110

10

0.313

40.350

111

10

0.375

47.900

112

10

0.500

62.460

113

10

0.625

76.330

114

12

0.250

39.430

115

12

0.313

48.860

116

12

0.375

58.100

117

12

0.500

76.070

118

12

0.625

93.340

119

14

0.313

57.360

120

14

0.375

68.310

121

14

0.500

89.680

122

14

0.625

110.230

123

16

0.313

65.870

124

16

0.375

78.520

125

16

0.500

103.300

126

16

0.625

127.340

127

18

0.500

116.910

128

18

0.625

144.390

129

18

0.750

171.160

130

18

0.870

197.240

131

20

0.500

130.520

132

20

0.625

161.400

133

20

0.750

191.580

134

20

0.875

221.060

135

22

0.750

212.000

136

22

0.875

244.880

Lists

Maybe I didn't... is that how it's supposed to look?

klwilliams13wf · Apr 5, 2021

Fluff said:
Try it like

Excel Formula:

=SUMPRODUCT((Lists!$A$2:$A$8=E2)*(Lists!$B$2:$B$8=C2)*(Lists!$C$2:$C$8=G2)*Lists!$D$2:$D$8)

It worked!! Thank you sooooo much!!

klwilliams13wf · Apr 5, 2021

klwilliams13wf said:
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?

kweaver said:
Book1
B C D E F G H I J K L M N O P Q R S T
1 QTY WIDTH UNIT DEPTH UNIT THICK UNIT LENGTH UNIT WELD EXT UNIT LBS/UNIT D W THICK WT/FT
2 10 1 1/2 in 1 1/2 in 0.083 IN 10 IN 100 1.602 1 1 0.12 1.437
3 4 4 0.375 17.27 1 1 0.075 0.945
4 3/4 3/4 0.06 0.552 5/8 5/8 0.0625 0.442
5 3/4 3/4 0.048 0.457
6 3/4 3/4 0.06 0.552
7 1 1/2 1 1/2 0.083 1.602
8 4 4 0.375 17.27
Sheet1
Cell Formulas
Range Formula
N2:N4 N2 =SUM(($Q$2:$Q$8=E2)*($R$2:$R$8=C2)*($S$2:$S$8=G2)*$T$2:$T$8)
Q4:R4 R4 =5/8
Q5:Q6 Q5 =3/4

I got the solution... thank you sooooo much for all your time, you are awesome!

Fluff · Apr 5, 2021

With the data like that you could also use

Excel Formula:

=SUMIFS(Lists!O:O,Lists!M:M,E2,Lists!L:L,G2,Lists!N:N,I2)

kweaver · Apr 5, 2021

klwilliams13wf said:
I figured out Xl2bb!

NEW TAKEOFF LAYOUTkw.xlsx
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD
1 Description Grade QTY WIDTH UNIT DEPTH UNIT THICKNESS UNIT LENGTH UNIT WELD SIZE EXTENSION UNIT LBS PER UNIT UNIT TOTAL LBS $/UNIT Custom $/Unit PLATE PROCESSING TOTAL MATERIAL TOTAL COST/UNIT LABOR TOTAL OVERALL TOTAL MAN HOURS
2 HSS Square Column 5 4 in 4 in 0.250 in 10 ft 50 ft 4 4 1/8 6.34 eq chart
HSS SQUARE COLUMN
Cell Formulas
Range Formula
F2 F2 =IF(D2="","",IF(A2="Weld Simpson Bucket","",IF(A2="Weld Base Plate","",IF(A2="Weld Top Plate","",IF(ISNUMBER(SEARCH("Column",A2)),"in",IF(ISNUMBER(SEARCH("holes",A2)),"dia",IF(ISNUMBER(SEARCH("studs",A2)),"dia",IF(ISNUMBER(SEARCH("bucket",A2)),"ea",IF(ISNUMBER(SEARCH("bolt",A2)),"dia",IF(ISNUMBER(SEARCH("plate",A2)),"in",IF(ISNUMBER(SEARCH("",A2)),"")))))))))))
H2,J2 H2 =F2
N2 N2 =K2*D2
Cells with Data Validation
Cell Allow Criteria
X2 List =Pricing
I2 List =Wall_Thickness

NEW TAKEOFF LAYOUTkw.xlsx
L M N O
3 1/2 1/2 0.048 0.301
4 1/2 1/2 0.060 0.362
5 5/8 5/8 0.048 0.367
6 5/8 5/8 0.060 0.442
7 3/4 3/4 0.048 0.457
8 3/4 3/4 0.060 0.552
9 3/4 3/4 0.075 0.690
10 3/4 3/4 0.083 0.753
11 3/4 3/4 0.090 0.800
12 3/4 3/4 0.120 1.029
13 1 1 0.048 0.629
14 1 1 0.060 0.763
15 1 1 0.075 0.945
16 1 1 0.083 1.036
17 1 1 0.090 1.115
18 1 1 0.120 1.437
19 1 1/4 1 1/4 0.048 0.807
20 1 1/4 1 1/4 0.060 0.981
21 1 1/4 1 1/4 0.075 1.200
22 1 1/4 1 1/4 0.083 1.318
23 1 1/4 1 1/4 0.090 1.443
24 1 1/4 1 1/4 0.120 1.846
25 1 1/4 1 1/4 0.188 2.400
26 1 1/2 1 1/2 0.048 0.956
27 1 1/2 1 1/2 0.060 1.163
28 1 1/2 1 1/2 0.075 1.455
29 1 1/2 1 1/2 0.083 1.602
30 1 1/2 1 1/2 0.090 1.716
31 1 1/2 1 1/2 0.120 2.254
32 1 1/2 1 1/2 0.188 3.230
33 1 1/2 1 1/2 0.250 3.700
34 1 3/4 1 3/4 0.060 1.370
35 1 3/4 1 3/4 0.083 1.884
36 1 3/4 1 3/4 0.090 2.011
37 1 3/4 1 3/4 0.120 2.663
38 2 2 0.060 1.564
39 2 2 0.075 1.966
40 2 2 0.083 2.143
41 2 2 0.090 2.316
42 2 2 0.120 3.050
43 2 2 0.188 4.320
44 2 2 0.250 5.410
45 2 2 0.313 6.320
46 2 1/2 2 1/2 0.090 2.979
47 2 1/2 2 1/2 0.120 3.890
48 2 1/2 2 1/2 0.188 5.590
49 2 1/2 2 1/2 0.250 7.110
50 2 1/2 2 1/2 0.313 8.450
51 3 3 0.120 4.700
52 3 3 0.188 6.870
53 3 3 0.250 8.810
54 3 3 0.313 10.580
55 3 3 0.375 11.750
56 3 1/2 3 1/2 0.120 5.520
57 3 1/2 3 1/2 0.188 8.150
58 3 1/2 3 1/2 0.250 10.510
59 3 1/2 3 1/2 0.313 12.700
60 3 1/2 3 1/2 0.375 14.710
61 4 4 0.120 6.340
62 4 4 0.188 9.420
63 4 4 0.250 12.210
64 4 4 0.313 14.830
65 4 4 0.375 17.270
66 4 4 0.500 21.630
67 4 1/2 4 1/2 0.120 7.310
68 4 1/2 4 1/2 0.188 10.700
69 4 1/2 4 1/2 0.250 13.910
70 4 1/2 4 1/2 0.313 16.980
71 4 1/2 4 1/2 0.375 19.820
72 5 5 0.120 7.840
73 5 5 0.188 11.970
74 5 5 0.250 15.620
75 5 5 0.313 19.080
76 5 5 0.375 22.370
77 5 5 0.500 28.430
78 5 1/2 5 1/2 0.125 9.010
79 5 1/2 5 1/2 0.165 11.740
80 5 1/2 5 1/2 0.188 13.300
81 5 1/2 5 1/2 0.250 17.320
82 5 1/2 5 1/2 0.313 21.220
83 5 1/2 5 1/2 0.375 24.930
84 5 1/2 5 1/2 0.500 31.840
85 6 6 0.188 14.530
86 6 6 0.250 19.020
87 6 6 0.313 23.340
88 6 6 0.375 27.480
89 6 6 0.500 35.240
90 6 6 0.625 42.300
91 7 7 0.188 17.080
92 7 7 0.250 22.420
93 7 7 0.313 27.590
94 7 7 0.375 32.580
95 7 7 0.500 42.050
96 7 7 0.625 50.760
97 8 8 0.188 19.630
98 8 8 0.250 25.820
99 8 8 0.313 31.840
100 8 8 0.375 37.690
101 8 8 0.500 48.850
102 8 8 0.625 59.320
103 9 9 0.250 29.230
104 9 9 0.313 36.100
105 9 9 0.375 42.790
106 9 9 0.500 55.660
107 9 9 0.625 67.820
108 10 10 0.188 24.750
109 10 10 0.250 32.630
110 10 10 0.313 40.350
111 10 10 0.375 47.900
112 10 10 0.500 62.460
113 10 10 0.625 76.330
114 12 12 0.250 39.430
115 12 12 0.313 48.860
116 12 12 0.375 58.100
117 12 12 0.500 76.070
118 12 12 0.625 93.340
119 14 14 0.313 57.360
120 14 14 0.375 68.310
121 14 14 0.500 89.680
122 14 14 0.625 110.230
123 16 16 0.313 65.870
124 16 16 0.375 78.520
125 16 16 0.500 103.300
126 16 16 0.625 127.340
127 18 18 0.500 116.910
128 18 18 0.625 144.390
129 18 18 0.750 171.160
130 18 18 0.870 197.240
131 20 20 0.500 130.520
132 20 20 0.625 161.400
133 20 20 0.750 191.580
134 20 20 0.875 221.060
135 22 22 0.750 212.000
136 22 22 0.875 244.880
Lists

Maybe I didn't... is that how it's supposed to look?

Sorry abou the mis-typing when it should have been SUMPRODUCT as Fluff pointed out.

klwilliams13wf · Apr 5, 2021

Fluff said:
With the data like that you could also use

Excel Formula:

=SUMIFS(Lists!O:O,Lists!M:M,E2,Lists!L:L,G2,Lists!N:N,I2)

And if I wanted to have the next cell point to another column in the same table, would I just add it like below?

=SUMPRODUCT((Lists!$M$3:$M$136=E2)*(Lists!$L$3:$L$136=G2)*(Lists!$N$3:$N$136=I2)*(Lists!$N$3:$N$136=I2)*(Lists!$O$3:$O$136=P2)*Lists!$S$3:$S$136)

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

klwilliams13wf

New Member

kweaver

Well-known Member

Excel Facts

kweaver

Well-known Member

klwilliams13wf

New Member

Fluff

MrExcel MVP, Moderator

klwilliams13wf

New Member

klwilliams13wf

New Member

klwilliams13wf

New Member

Fluff

MrExcel MVP, Moderator

kweaver

Well-known Member

klwilliams13wf

New Member

Similar threads

Forum statistics

Share this page

Cell Formulas
Range	Formula
N2:N4	N2	=SUM(($Q$2:$Q$8=E2)($R$2:$R$8=C2)($S$2:$S$8=G2)*$T$2:$T$8)
Q4:R4	R4	=5/8
Q5:Q6	Q5	=3/4

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

New Member

Well-known Member

Excel Facts

Well-known Member

New Member

MrExcel MVP, Moderator

New Member

New Member

New Member

MrExcel MVP, Moderator

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock