Notifications
Clear all
Topic starter
Do you know how to use the tool of index and match to find out the item with more multiple conditions?
I have a sample in the work as attachment which I have to fill up the students name under the course. The data base is in table 1. Then I have to set a formula into table 2 to draw the student names under the course.
I don't know how to use index and match together. Or do you have better idea to do it?
| Table1 | Course | |||||||
| Name | MA1 | MA2 | MA3 | MA4 | MA5 | MA6 | MA7 | MA8 |
| John | √ | √ | √ | √ | ||||
| Rosa | √ | √ | √ | √ | √ | √ | ||
| Linda | √ | √ | √ | |||||
| Shelley | √ | √ | √ | √ | √ | √ | ||
| Romanda | √ | √ | √ | √ | √ | √ | ||
| Beth | √ | √ | √ | √ | √ | √ | ||
| Ryan | √ | √ | √ | √ | ||||
| Brandon | √ | √ | √ | √ | √ | |||
| Kate | √ | √ | √ | √ | ||||
| Jessie | √ | √ | ||||||
| Table 2 | MA1 | MA2 | MA3 | MA4 | MA5 | MA6 | MA7 | MA8 |
| Rosa | John | Linda | Rosa | John | John | Rosa | John | |
| Shelley | Rosa | Shelley | Linda | Rosa | Rosa | Linda | Shelley | |
| Ryan | Shelley | Beth | Romanda | Shelley | Romanda | Shelley | Romanda | |
| Kate | Romanda | Ryan | Beth | Romanda | Brandon | Romanda | Beth | |
| Jessie | Beth | Brandon | Ryan | Beth | Kate | Beth | Brandon | |
| Brandon | Jessie | kate | Brandon | Ryan | Kate | |||
Posted : 20/10/2017 1:21 pm
Hi Lucy
You can take a look at the attachment.
It uses array formulas. You can read about array formulas here https://www.myonlinetraininghub.com/excel-array-formula
You may have to modify the range to suit your needs. You will need to press CTRL+SHIFT+ENTER after entering the formula.
Hope this helps.
Sunny
Posted : 20/10/2017 9:49 pm
Topic starter
Hi Sunny,
It is very helpful! Thank you very much!!!
Have a good day,
Lucy
Posted : 20/10/2017 9:59 pm
