Help with lookup

[kris02]

Hi, I have the following data. I select level and give the flags as Y or N. if I select level 1 and flag as N, it should show the points related to that level in points column for each activity else it should show the points related to next level in points column. Following is the data I have.[TABLE="width: 549"]
<tbody>[TR]
[TD][TABLE="width: 485"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Level[/TD]
[/TR]
[TR]
[TD]Activity[/TD]
[TD]Level 1[/TD]
[TD]Level 2[/TD]
[TD]Level 3[/TD]
[TD]Level 4[/TD]
[TD]Flag[/TD]
[TD]Points[/TD]
[/TR]
[TR]
[TD]Task 1[/TD]
[TD="align: right"]0.167[/TD]
[TD="align: right"]0.139[/TD]
[TD="align: right"]0.122[/TD]
[TD="align: right"]0.111[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task 2[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]0.238[/TD]
[TD="align: right"]0.225[/TD]
[TD="align: right"]0.213[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task 3[/TD]
[TD="align: right"]0.33[/TD]
[TD="align: right"]0.317[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0.283[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Task 4[/TD]
[TD="align: right"]1.101[/TD]
[TD="align: right"]1.003[/TD]
[TD="align: right"]0.924[/TD]
[TD="align: right"]0.859[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Following is the expected result for level 1 and level 3 with different flags.

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl182, width: 64"][/TD]
[TD="class: xl182, width: 64"]Level 1[/TD]
[TD="width: 64"][/TD]
[TD="class: xl182, width: 64"][/TD]
[TD="class: xl182, width: 64"]Level 3[/TD]
[/TR]
[TR]
[TD="class: xl183, width: 64"]Flag[/TD]
[TD="class: xl183, width: 64"]Points[/TD]
[TD][/TD]
[TD="class: xl188, width: 64"]Flag[/TD]
[TD="class: xl183, width: 64"]Points[/TD]
[/TR]
[TR]
[TD="class: xl186"]N[/TD]
[TD="class: xl185, align: right"]0.167[/TD]
[TD][/TD]
[TD="class: xl189"]Y[/TD]
[TD="class: xl185, align: right"]0.111[/TD]
[/TR]
[TR]
[TD="class: xl186"]N[/TD]
[TD="class: xl187, align: right"]0.25[/TD]
[TD][/TD]
[TD="class: xl189"]N[/TD]
[TD="class: xl184, align: right"]0.225[/TD]
[/TR]
[TR]
[TD="class: xl186"]Y[/TD]
[TD="class: xl184, align: right"]0.317[/TD]
[TD][/TD]
[TD="class: xl189"]Y[/TD]
[TD="class: xl184, align: right"]0.283[/TD]
[/TR]
[TR]
[TD="class: xl186"]N[/TD]
[TD="class: xl184, align: right"]1.101[/TD]
[TD][/TD]
[TD="class: xl189"]N[/TD]
[TD="class: xl184, align: right"]0.924[/TD]
[/TR]
</tbody>[/TABLE]

Help me with the formula. I could write half. I mean for flag N but how to write for flag Y as it has to display the value for next level.

Thanks!

 

[steve the fish]

I don't see any values for flag in your data table nor do I see any tasks in your results table. Am I missing something?

 

[kris02]

I don't see any values for flag in your data table nor do I see any tasks in your results table. Am I missing something?

Hi, I have given different flags and the expected points for level 1 and level 3 in the 2nd table. If I give those flags for level 1 and level 3 it should show the points as shown in table 2.

In table 1, based on the flags and level which are to be input, points are to be displayed.

Please let me know if I am not clear.

 

[kris02]

In the 2nd table, 4 rows for 4 different tasks (1 to 4). Sorry missed adding activity column in 2nd table

 

[steve the fish]

This will do that I think. I pasted data table in A1:E6 and the table to fill in in A9:B14. In B11 and copy down:

=INDEX($A$2:$E$6,ROWS($A$1:A2),MATCH(B$9,$A$2:$E$2,0)+(A11<>"N"))

 

[kris02]

This will do that I think. I pasted data table in A1:E6 and the table to fill in in A9:B14. In B11 and copy down:

=INDEX($A$2:$E$6,ROWS($A$1:A2),MATCH(B$9,$A$2:$E$2,0)+(A11<>"N"))

Thank you very much. It worked.

If you don't mind, can you explain how this formula works. Please excuse my ignorance.