CODEWITHSUNDEEP
excelexcel-formula
Fabman
Fabman

Reputation: 373

How to create Filtered list if values match with formulas?

I got a list of data with three columns, Name, Data and Groups. I want automatically create a list with the names on the column B that match a value on the column C.

I'm using Index and Match to check if the value matches and get the name, and then I use the tool filter to hide the #N/A. But I would like to get the list like the column "I" without having to hide rows.

enter image description here

edit:

I didn't showed the formula because is an spanish excel version, but in case anyone wonders I add this formula per cell:

spanish " =INDICE(B4,COINCIDIR(234,C4,0))"

translation " =INDEX(B4,MATCH(234,C4,0))"

Upvotes: 1

Views: 191

Answers (1)

Rohit Lodha
Rohit Lodha

Reputation: 43

Consider a case (the range of the below data is A1:D6)-

Name   Value   Common_name_with_blanks   Common_name_without_blanks
A       10               A                          A  
B       20                                          C
C       10               C                          E
D       25                                          
E       10               E

For Common_name_with_blanks you can use the formula in the first cell and drag it down-

 =IF(COUNTIF($B$2:$B$7,B2)=1,"",$A2)

Where, $B$2:$B$7 is the range of the column "Value".

For creating the last/required column, we will use an array formula.

={IFERROR(INDEX($C$2:$C$7,SMALL((IF(LEN($C$2:$C$7),ROW(INDIRECT("1:"&ROWS(C2:C7))))),ROW(A1)),1),"")}

The range $C$2:$C$7 refers to the Common_name_with_blank column. Also, as this is an array formula, do not forget to press shift+ctrl+enter (on windows) while entering it.

Upvotes: 1

Related Questions