Hi, here is what I intended to do using INDEX ad MATCH.
1. I would like to show the whole row result. Example in yellow highlight, input formula in I9 and it show the rest of the info in the row (like array result).
2. There are multiple matching conditions and in each condition there are more than 1 value to match (list of value is dynamic, sometime there is only 1 value or sometime there can be 4 values in each list).
3. There could be more than 1 result.
So far I'm able to match multiple condition and return multiple result using the formula below (in green highlight), but I'm not able to match more than 1 value in each condition and return the whole row, is it possible to do that? Any suggestion will be much appreciated.
={IFERROR(INDEX($C$2:$C$16,SMALL(IF((INDEX($C$2:$G$16,,4)=$J$3)*(INDEX($C$2:$G$16,,5)=$M$3),MATCH(ROW($C$2:$G$16),ROW($C$2:$G$16)),""),ROWS(I$17:I17)),COLUMNS($A$1:$A1)),"")}
Note: I'm using Excel 2013, so any fancy 365 formula won't work.
Thank you in advance for your help.
1. I would like to show the whole row result. Example in yellow highlight, input formula in I9 and it show the rest of the info in the row (like array result).
2. There are multiple matching conditions and in each condition there are more than 1 value to match (list of value is dynamic, sometime there is only 1 value or sometime there can be 4 values in each list).
3. There could be more than 1 result.
So far I'm able to match multiple condition and return multiple result using the formula below (in green highlight), but I'm not able to match more than 1 value in each condition and return the whole row, is it possible to do that? Any suggestion will be much appreciated.
={IFERROR(INDEX($C$2:$C$16,SMALL(IF((INDEX($C$2:$G$16,,4)=$J$3)*(INDEX($C$2:$G$16,,5)=$M$3),MATCH(ROW($C$2:$G$16),ROW($C$2:$G$16)),""),ROWS(I$17:I17)),COLUMNS($A$1:$A1)),"")}
Note: I'm using Excel 2013, so any fancy 365 formula won't work.
Thank you in advance for your help.