I am trying to do a search that is returning the incorrect result. Here is the data.
Sheet1
<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 164px"> <col style="WIDTH: 76px"></colgroup> <tbody>
</tbody>
Here is my formula:
The result I'm receiving A. The correct result is B and D since "Car" and House" are in rows 3 and 5.
The search part
is assigning 1 to "Car" and 2 to "House", which equals 3.
How do I correct my formula or is there a solution?
Sheet1
B | C | |
2 | Dog Cat | A |
3 | Zebra House Car | B |
4 | Mouse Computer Keyboad | C |
5 | Car House | D |
6 | ||
7 | Correct Result | |
8 | B | |
9 | D |
<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 164px"> <col style="WIDTH: 76px"></colgroup> <tbody>
</tbody>
Here is my formula:
Code:
=INDEX($C$2:$C$5,SMALL(IF(SUMPRODUCT(NOT(ISERR(SEARCH({"Car","House"},$B$2:$B$5)))*{1,2})=3=TRUE,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($B$12:$B12)))
The result I'm receiving A. The correct result is B and D since "Car" and House" are in rows 3 and 5.
The search part
Code:
SEARCH({"Car","House"},$B$2:$B$5)))*{1,2})=3
How do I correct my formula or is there a solution?