Index, match functions

coxash123

New Member
Joined
Aug 16, 2016
Messages
37
ACells, tissues and systems
1st
2nd
3rd
4rth

<tbody>
</tbody>

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.
IndexTopicstepcomment 1comment 2
ACells, tissues and systems3rdMulti organsRespiration
BSpace and the universe1stIts a big placeIts expanding
CChemical reactions2ndIts goes bang Whiz pop

<tbody>
</tbody>


thanks in advance,

Ashley
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hello,

To better describe your expected result ...you should be describe more precisely what is the actual result you are looking for ...:wink:
 
Upvote 0
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.
 
Upvote 0
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 ....
 
Upvote 0
Sadly the Forum does not allow to attach files ...:(
 
Upvote 0
Given the three dimensions you are dealing with ... you will need an Array Formula

Code:
=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
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top