CODEWITHSUNDEEP
excelexcel-formula
MAA
MAA

Reputation: 45

How can I get a column "bonded" with an INDEX function into a formula in Excel?

I currently have this table on Excel:

enter image description here

I have applied this formula to the last column, so that it matches the first and the second column:

=IF(ISNA(MATCH(A6;$B$1:$B$6;0));"";INDEX($A$1:$B$6;MATCH(A6;$B$1:$B$6;0);2))

How can I get the third column into the upper formula so that it appears next to the matching names? (The idea is that the second and the third column are bonded)

Like this:

enter image description here

Upvotes: 0

Views: 67

Answers (2)

P.b
P.b

Reputation: 11588

Instead of INDEX/MATCH this can be accomplished with FILTER/COUNTIF: =FILTER(B1:C6,COUNTIF(A1:A6,B1:B6)) It filters the given range B1:C6 where COUNTIF(A1:A6,B1:B6) equals 1. enter image description here

PS If you would want the list of non-matching values you can use the same but you could add =0: =FILTER(B1:C6,COUNTIF(A1:A6,B1:B6)=0)

Office365 is required for this solution.

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

Minor change:

=IF(ISNA(MATCH(A6;$B$1:$B$6;0));"";INDEX($A$1:$C$6;MATCH(A6;$B$1:$B$6;0);{2,3}))

Example at different address:

=IF(ISNA(MATCH(A21,$B$21:$B$26,0)),"",INDEX($A$21:$C$26,MATCH(A21,$B$21:$B$26,0),{2,3}))

enter image description here

In current versions of Excel, the 2nd column will SPILL. In older versions, you may need to select both columns and enter it as an array formula with ctrl+shift+enter

Upvotes: 1

Related Questions