Index, match functions

[coxash123]

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]Cells, tissues and systems[/TD]
[/TR]
[TR]
[TD]1st[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2nd[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3rd[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4rth[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to create a formula - in which you can use drop-down menus for cells A1 and B1. These will then, in turn, look up another table and look up the criteria in cell A1 and B2. I also want it to look at the steps (A2:A5) and return the appropriate comments for the given criteria. I know this might be a match and index formula. but struggling.

The below table is on another tab.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Index[/TD]
[TD]Topic[/TD]
[TD]step[/TD]
[TD]comment 1[/TD]
[TD]comment 2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Cells, tissues and systems[/TD]
[TD]3rd[/TD]
[TD]Multi organs[/TD]
[TD]Respiration[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Space and the universe[/TD]
[TD]1st[/TD]
[TD]Its a big place[/TD]
[TD]Its expanding[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Chemical reactions[/TD]
[TD]2nd[/TD]
[TD]Its goes bang [/TD]
[TD]Whiz pop[/TD]
[/TR]
</tbody>[/TABLE]


thanks in advance,

Ashley

 

[James006]

Hello,

To better describe your expected result ...you should be describe more precisely what is the actual result you are looking for ...:wink:

 

[coxash123]

basically, I am a class teacher. I want to have a set of comments for each subject on a separate tab in excel. I then want to have a comment table for each test where you set the conditions. e.g topic, and index. I then want them to look up the comments in the comments table and retrieve the appropriate comments for each step.
hope this clarifies.

 

[James006]

Thanks for your message ...

In order to clarify things :

1. Describe the structure of your Sheet 1

2. Describe the structure of your Sheet 2

3. Describe one example of your expected result ... i.e. what is the input and what is the expected output ....

 

[coxash123]

How do i attach a file? I really do appreciate your help.

 

[James006]

Sadly the Forum does not allow to attach files ...

 

[AlanY]

How do i attach a file? I really do appreciate your help.

you can share a file in Google drive, or similar

 

[James006]

Given the three dimensions you are dealing with ... you will need an Array Formula

=IFERROR(INDEX(Threads!$H$1:$H$1346,SMALL(IF(Threads!$B$1:$B$1346=$C$5,IF(Threads!$C$1:$C$1346=$B$5,IF(Threads!$G$1:$G$1346=$A$6,ROW(Threads!$A$1:$A$1346)))),ROWS(C$6:C6)))&CHAR(10)&INDEX(Threads!$I$1:$I$1346,SMALL(IF(Threads!$B$1:$B$1346=$C$5,IF(Threads!$C$1:$C$1346=$B$5,IF(Threads!$G$1:$G$1346=$A$6,ROW(Threads!$A$1:$A$1346)))),ROWS(C$6:C6)))&CHAR(10)&INDEX(Threads!$J$1:$J$1346,SMALL(IF(Threads!$B$1:$B$1346=$C$5,IF(Threads!$C$1:$C$1346=$B$5,IF(Threads!$G$1:$G$1346=$A$6,ROW(Threads!$A$1:$A$1346)))),ROWS(C$6:C6))),"")

Hope this will help

 

[James006]

Glad you have managed to sort it out ..