Look up against two columns and maybe 1 row

T

Turnipboy

I have various jet engine stages (ES) that vibrate at various modes (M)
to varying degrees at different positions (P1, P2, P3). In another
sheet I have varying ES's, M's and positions in cells, how can I then
look up the correct degree of vibration? e.g. for engine stage 1 for
vibration mode 2 at position 2, I want to be able to look up the answer
81 automatically. Thanks.

ES M P1 P2 P3
0 1 678 890 890
0 2 67 980 88
0 3 6 89 90
1 1 789 9 90
1 2 8 81 78
2 1 7 9 788
2 2 78 90 906
2 3 7 88 78
2 4 89 9 890
3 1 7 89 8
 
K

Ken Wright

With your table in A1:E11 and the following Labels ES / M / P / V in G2:G5
respectively, and then in H2:H4 your 1 / 2 / 2 values.

In cell H5 put the following formula, and then simply adjust the variables
and ranges to suit your own

=SUMPRODUCT(($A$2:$A$11=H2)*($B$2:$B$11=H3)*(OFFSET($A$2,,1+H4,COUNTA($A$2:$A$11),1)))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
M

Max

One way ...

Sample construct at:
http://cjoint.com/?lulEKpP7ft
LookUp_3Cols_Turnipboy_gen.xls

Assuming source table is in Sheet1, cols A to E, data from row2 down
ES M P1 P2 P3
0 1 678 890 890
0 2 67 980 88
0 3 6 89 90
1 1 789 9 90
Click to expand...
etc

In Sheet2,
Labels placed in A1:C1 are: ES, M, P
with the values for ES, M, P listed from row2 down

ES M P
1 2 2 ?
2 4 3 ?
3 1 2 ?
0 2 1 ?

To retrieve the lookup results from Sheet1,
put in the formula bar for D2,
then array-enter the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):

=IF(COUNT(A2:C2)<3,"",
INDEX(OFFSET(Sheet1!A:A,,MATCH($C$1&C2,Sheet1!$1:$1,0)-1),
MATCH(1,(Sheet1!$A$1:$A$100=A2)*(Sheet1!$B$1:$B$100=B2),0)))

Copy D2 down

Col D will return the desired results

Adapt the ranges in the formula to suit (must be identical size):
Sheet1!$A$1:$A$100
Sheet1!$B$1:$B$100

Note that the formula must be array-entered each time, if it is subsequently
edited
 
K

Ken Wright

Should have said - assumes there is only 1 answer for any given combo.

Regards
Ken..................
 
T

Turnipboy

Wow, Thanks everyone.

Max's table works better than I had hoped, it allows be to add modes to
each stage without worrying too much about editting formulae.

Thanks again everyone.
 
D

Domenic

Here's another way, which eliminates the volatile function OFFSET...

Assumptions:

A1:E1 contains your headers/labels

A2:E11 contains your data

G2:I2 contains your criteria, such as 1, 2, and P2, respectively

Formula:

=SUMPRODUCT(--($A$2:$A$11=G2),--($B$2:$B$11=H2),INDEX($C$2:$E$11,0,MATCH(
I2,$C$1:$E$1,0)))

Hope this helps!
 
T

Turnipboy

Thanks.

Could someone please email Max's workbook to me as I am at work now and
the damn firewall will not let me access that page.

My email address is:

[email protected]

Thanks again.
 
Top