0

I am looking for returning multiple column values using multiple matching criteria. Attached is a screenshot of sample sheet, which have my criteria on cell's B1 & C1. So basically, when matching 2 criteria (example "Team1" & "low"), it should return columns header (example Name10 & name14) from the header ranger C3:N3.

I have tried a couple of formulas, and is is how far I gone: =INDEX($C$2:$AL$2,SMALL(IF(($A$3:$A$21=$B$1)*($B$3:$B$21=$C$1),ROW($A$3:$A$21)-ROW($A$3)+1),ROW(1:1)))

I am not sure what is missing?

enter image description here

enter image description here

Thanks in advance Fox

foxx
  • 11
  • 2
  • Can't understand what do you expect to obtain and where. When the two criteria you specifyed in cell B1 and C1 are matched (both or just one of them?) in the cloumns A and B, what names of the row 2 do you expect to obtain? The ones with a corresponding non-null value in the C3:AC21 range? Any difference if the corresponding number is 1, 2 3 or other? And...where are you going to input the formula? Where you want the output? – EttoreP Apr 10 '16 at 13:43
  • Both crititera in B1 & C1 must be matched. I am expecting to obtain the header column only (which is a list of names, for ex header1 or header 7) as a result of the matching and not the row value (C3:AC21). As where to have the forumla, it can be in A23, and draged down to A30. Thank you – foxx Apr 11 '16 at 07:30
  • Ok but what names do you expect to obtain of the header column when there is a match in the A and B columns? Are the names with a number >0 associated to?Could you make a couple of examples of the result that you expect? For example with "team2" and "low" or "team3 and "mid"? – EttoreP Apr 11 '16 at 09:59
  • What do you want to happen when your criteria does not exist? As in your screen shot "Team 1" and "Low", I did not see a Team 1 and Low in the table below. – Forward Ed Apr 11 '16 at 10:06
  • What I want to happen is when both criteria matched (for ex "team 2 & low" i should get a result of " name4 & name6) if no match is found return blank cell. – foxx Apr 13 '16 at 07:09
  • I'm sorry but i have not yet understand what's the criteria for select the names...do you want the formula gives back all the names where there the intersection cell between the name and the criteria contains a number >0? In the example you made, with team2&low why the formula should give you name4 and name6?Can't understand the logic of that, sorry – EttoreP Apr 14 '16 at 17:18
  • Sorry for any confusion caused Ettore. I have uploaded another pic that hopefully explain what I am looking for. http://tinypic.com/r/250j7gy/9 what I am looking for is to match B1&C1 from range A2:N10, if match is found then return multiple column ( "name10" & "name14"). If no match return blank. as for criteria >0, in B3 i have formula to return "low" if count value >0 &=1 [ =IF(COUNTIF(C3:N3,">0")=1,"Low",)], Thank you – foxx Apr 15 '16 at 22:51

1 Answers1

0

First of all, in your example you point out row 3 and 4 but only one of the specified criteria are matched in this rows: low, because Team4 specifyed in the criteria it's not matched, so i will consider you are looking to match one OR both the criteria specified.
The only way i can imagine for do this with a formula is to use a formula like this 

=SE(C3<>0;$C$2&", ";"")&SE(D3<>0;$D$2&", ";"")&SE(E3<>0;$E$2&", ";"")&SE(F3<>0;$F$2&", ";"")&SE(G3<>0;$G$2&", ";"")&SE(H3<>0;$H$2&", ";"")&SE(I3<>0;$I$2&", ";"")&SE(J3<>0;$J$2&", ";"")  'and so on...

where SE() it's function IF() in my language, with this formula in a column on the right of the table (for example col O) you will have a list of the names of that row where the corresponding number is different from 0...expand the formula down for all the rows and then, with a formula like this

=SE(O(A1=A3;B1=B3);O3;"")&SE(O(A1=A4;B1=B4);O4;"")&SE(O(A1=A5;B1=B5);O5;"")&SE(O(A1=A6;B1=B6);O6;"")&SE(O(A1=A7;B1=B7);O7;"") 'and so on...

with the function O() corresponding to OR() you will concatenate the strings (names) of the rows that match one OR both the criteria. If you whant to match both the criteria you should use AND() instead of OR().
The problem of this approach is that the formula becomes very long if you have a lot of names and a lot of rows, and if you add rows you have to modify the formula. Another problem is that if you match the same name more times it will be repeated in the list that the formula outputs...and the list of the names ends with a comma.

In fact, i can't tell that this is a good way for obtain what you need, but it's the only i can imagine only with formulas.

If you should use a macro the problem would be solved better and in a more flexible way, should you?

EttoreP
  • 404
  • 6
  • 16