Look up against two columns and maybe 1 row

**Turnipboy** · 11-20-2005, 05:24 AM

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 21 32 33

Sorry if the columns are not clear.
ES goes 0,0,0,1,1,2,2....
M goes 1,2,3,1,2,1,2....
P1 goes 678,67,6,789...
and so on

Ken Wright · 11-20-2005, 06:15 AM

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 :-)
------------------------------*------------------------------*----------------

"Turnipboy" <Turnipboy.1ysgjm_1132478701.137@excelforum-nospam.com> wrote in
message news:Turnipboy.1ysgjm_1132478701.137@excelforum-nospam.com...
>
> 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
>
>
> --
> Turnipboy
> ------------------------------------------------------------------------
> Turnipboy's Profile:
> http://www.excelforum.com/member.php...o&userid=24527
> View this thread: http://www.excelforum.com/showthread...hreadid=486632
>

Max · 11-20-2005, 06:35 AM

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
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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Turnipboy" <Turnipboy.1ysgjm_1132478701.137@excelforum-nospam.com> wrote in
message news:Turnipboy.1ysgjm_1132478701.137@excelforum-nospam.com...
>
> 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
>
>
> --
> Turnipboy
> ------------------------------------------------------------------------
> Turnipboy's Profile:
http://www.excelforum.com/member.php...o&userid=24527
> View this thread: http://www.excelforum.com/showthread...hreadid=486632
>

Ken Wright · 11-20-2005, 07:00 AM

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

Regards
Ken..................

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:OGT7Srb7FHA.3880@TK2MSFTNGP12.phx.gbl...
> 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 :-)
> ------------------------------*------------------------------*----------------
>
>
>
> "Turnipboy" <Turnipboy.1ysgjm_1132478701.137@excelforum-nospam.com> wrote
> in message news:Turnipboy.1ysgjm_1132478701.137@excelforum-nospam.com...
>>
>> 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
>>
>>
>> --
>> Turnipboy
>> ------------------------------------------------------------------------
>> Turnipboy's Profile:
>> http://www.excelforum.com/member.php...o&userid=24527
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=486632
>>
>
>

**Turnipboy** · 11-20-2005, 07:09 AM

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.

Max · 11-20-2005, 07:20 AM

Great to hear that !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Turnipboy" <Turnipboy.1yslem_1132485001.1991@excelforum-nospam.com> wrote
in message news:Turnipboy.1yslem_1132485001.1991@excelforum-nospam.com...
>
> 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.

Domenic · 11-20-2005, 10:55 AM

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!

In article <Turnipboy.1ysgjm_1132478701.137@excelforum-nospam.com>,
Turnipboy <Turnipboy.1ysgjm_1132478701.137@excelforum-nospam.com>
wrote:

> 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

**Turnipboy** · 11-21-2005, 06:06 AM

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:

******** (thanks, received)

Thanks again.