excel use three values to return one 1

[conveyorbud]

using the values in column 1 and column 2 to select a unique row and a value in row one to return a single number

example input column 1 = 12 / input in column 2 = 3 / input to row 1 = 38 would return 125


0 0 20 26 32 38 44 51
10 2.50 60 75 85 100 110 120
10 3.50 75 85 95 110 120 130
12 2.50 70 85 100 115 120 140
12 3.00 80 100 110 125 130 145
12 3.50 95 110 120 135 145 160
14 2.50 85 100 120 145 165 195
14 3.00 95 115 130 155 180 210
14 3.50 105 130 150 175 190 220
14 4.00 130 150 170 200 210 235
16 2.50 105 130 150 175 200 235
16 3.00 115 140 165 185 220 250
16 3.50 130 155 170 200 225 260
16 4.00 160 185 205 225 245 280
18 2.50 125 160 185 215 240 280
18 3.00 135 180 205 230 250 290

 

[yakpol]

conveyorbud,
Your problem takes a few indexing formulas. Find a solution at:

http://www.yakpol.net/engtips/engtips_answer.xls

yakpol

 

[yogia]

Hi Conveyorbud:

Let us say your data columns have range names Column1, column2, ... through column8, then you can use the following array formula in EXCEL ...

=INDEX(Column6,MATCH(12&CHAR(1)&3,Column1&CHAR(1)&Column2,0))

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi Conveyorbud:

In the solution I posted above ...

=INDEX(Column6,MATCH(12&CHAR(1)&3,Column1&CHAR(1)&Column2,0))

it uses 12 and 3 as MATCH values in column1 and column2 to locate the corresponding value in column6. I did not follow your statement regarding

input to row 1 = 38

Please explain what this one means and where is 38 in the data that you posted.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[cummings54]

Assuming the data you provided is entered in a1:h16, then

=INDEX(C2:H16,MATCH(1,(A2:A16=12)*(B2:B16=3),0),MATCH(38,C1:H1,0))

returns 125
The formula must be entered as an array formula using the Ctrl-Shift-Enter. Of course you can replace the 3,12 and 38 with cell references.

 

[yakpol]

Cummings54,
A star for a super sleek solution!

yakpol

 

[conveyorbud]

thanks cummings54, that worked slick!