Multistep formula challenge
 

I need a multiple step formula for Excel 2003 that looks for a number Column
L for an occurrence of a number. There can potentially be many blanks in
between numbers but when it hits a number- say (L405) then it should look at
the corresponding row in B405 for the site name "text". Then index Column S
for the match to B405 "text". Once it finds the match in column S it need to
grab the value for corresponding row in Column T. Then find the % of the
original number (L405) of the value in Column T.

Formula is for Column M
1. Search Column L for number
2. Look in Column B for corresponding row €œtext€�
3. Look in Column S for €œtext€� match from Column B
4. When match is found take corresponding row in Column T
5. Find % of Column L value of Column T value

COL B L M S T
Site Species X Site % Site2 Site Total
s4910 s4910 560
s4965 s4965 39
s8135 s8135 400
s8457 s8457 501
s8077 4 0.020 s8077 200
s8723 s8723 568
s8483 3 0.042 s8483 70
s8626 s8626 98

Thank you

Multistep formula challenge
 

=A1/INDEX(T:T,MATCH(INDEX(B:B,
MATCH(A1,L:L,0)),S:S,0))

The two A1 references are the "number" you're starting the search looking
for. Be sure to format the cell with this formula as Percentage.

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JB Akron" wrote:

I need a multiple step formula for Excel 2003 that looks for a number Column
L for an occurrence of a number. There can potentially be many blanks in
between numbers but when it hits a number- say (L405) then it should look at
the corresponding row in B405 for the site name "text". Then index Column S
for the match to B405 "text". Once it finds the match in column S it need to
grab the value for corresponding row in Column T. Then find the % of the
original number (L405) of the value in Column T.

Formula is for Column M
1. Search Column L for number
2. Look in Column B for corresponding row €œtext€�
3. Look in Column S for €œtext€� match from Column B
4. When match is found take corresponding row in Column T
5. Find % of Column L value of Column T value

COL B L M S T
Site Species X Site % Site2 Site Total
s4910 s4910 560
s4965 s4965 39
s8135 s8135 400
s8457 s8457 501
s8077 4 0.020 s8077 200
s8723 s8723 568
s8483 3 0.042 s8483 70
s8626 s8626 98

Thank you

Multistep formula challenge
 

With your dataset titles in row 1 and data starting in row 2, this was the
formula for M2, then just copy it down:

=IF(L2="","",L2/INDEX(T:T,MATCH(INDEX(B:B,MATCH(L2,L:L,0)),S:S,0)) )

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JB Akron" wrote:

I need a multiple step formula for Excel 2003 that looks for a number Column
L for an occurrence of a number. There can potentially be many blanks in
between numbers but when it hits a number- say (L405) then it should look at
the corresponding row in B405 for the site name "text". Then index Column S
for the match to B405 "text". Once it finds the match in column S it need to
grab the value for corresponding row in Column T. Then find the % of the
original number (L405) of the value in Column T.

Formula is for Column M
1. Search Column L for number
2. Look in Column B for corresponding row €œtext€�
3. Look in Column S for €œtext€� match from Column B
4. When match is found take corresponding row in Column T
5. Find % of Column L value of Column T value

COL B L M S T
Site Species X Site % Site2 Site Total
s4910 s4910 560
s4965 s4965 39
s8135 s8135 400
s8457 s8457 501
s8077 4 0.020 s8077 200
s8723 s8723 568
s8483 3 0.042 s8483 70
s8626 s8626 98

Thank you

Multistep formula challenge
 

This was exactly right. Amazing Thank you.

"JBeaucaire" wrote:

With your dataset titles in row 1 and data starting in row 2, this was the
formula for M2, then just copy it down:

=IF(L2="","",L2/INDEX(T:T,MATCH(INDEX(B:B,MATCH(L2,L:L,0)),S:S,0)) )

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JB Akron" wrote:

I need a multiple step formula for Excel 2003 that looks for a number Column
L for an occurrence of a number. There can potentially be many blanks in
between numbers but when it hits a number- say (L405) then it should look at
the corresponding row in B405 for the site name "text". Then index Column S
for the match to B405 "text". Once it finds the match in column S it need to
grab the value for corresponding row in Column T. Then find the % of the
original number (L405) of the value in Column T.

Formula is for Column M
1. Search Column L for number
2. Look in Column B for corresponding row €œtext€�
3. Look in Column S for €œtext€� match from Column B
4. When match is found take corresponding row in Column T
5. Find % of Column L value of Column T value

COL B L M S T
Site Species X Site % Site2 Site Total
s4910 s4910 560
s4965 s4965 39
s8135 s8135 400
s8457 s8457 501
s8077 4 0.020 s8077 200
s8723 s8723 568
s8483 3 0.042 s8483 70
s8626 s8626 98

Thank you