I am trying to pull specific data from a large data set. I am currently using a index/small/row function with multiple if statements. However, this is only giving me exact matches. For instance, if I am looking for "Aerospace" in cell A2, my current formula does not return a value with "Aerospace,Alcoholic Beverages" or any other non-exact match. Here is my formula that returns exact matches only:
{=IF(AND(OR($A$2<>"-",$B$2<>"-"),OR($A$2<>"-",$C$2<>"-"),OR($B$2<>"-",$C$2<>"-"),OR($A$2<>"-",$B$2<>"-",$C$2<>"-")),">1 Ind",IF(AND($A$2="-",$B$2="-",$C$2="-"),"Ind?",IF($A$2<>"-",INDEX('Q4 ''11'!$A$1:$H$10000,SMALL(IF('Q4 ''11'!$A$1:$H$10000=$A$2,ROW('Q4 ''11'!$A$1:$H$10000)),ROW(1:1)),1),IF($B$2<>"-",INDEX('Q4 ''11'!$A$1:$H$10000,SMALL(IF('Q4 ''11'!$A$1:$H$10000=$B$2,ROW('Q4 ''11'!$A$1:$H$10000)),ROW(1:1)),1),INDEX('Q4 ''11'!$A$1:$H$10000,SMALL(IF('Q4 ''11'!$A$1:$H$10000=$C$2,ROW('Q4 ''11'!$A$1:$H$10000)),ROW(1:1)),1)))))}
The A2 - C2 references are drop-down menus. My version of Excel doesn't allow for the number of drop down menus that I need. That's why I need the if statements.
I have a few formulas on hand to find non-exact matches, but I can't seem to integrate them. Hopefully, this will make an answer more readily available:
IF(--(COUNTIF('xxx'!J23,"*yyy.com/user*")>0)=1
IFERROR(FIND($D$1,A1),0)>0
IF(FIND($A$2,'Q4 ''11'!$H2)=1,$A$2,0)
Any help is appreciated.
Bookmarks