CODEWITHSUNDEEP
excel
Navy
Navy

Reputation: 59

Excel:comparing two columns and return matched value in a single cell

I'm trying to match two columns and get the matched value in a single cell. For eg.

Column 1 - 8, 5, 7, AA, 10

Column2 - AA, DET, V, H, PED

8    AA
5    DET
7    V
AA   H
10   PED

Value returned - AA (This is the value that i want to get since this is common in both columns)

I tried using Index and match functions, but they are not able to match the two given range. Please help. Thanks in advance.

Upvotes: 0

Views: 166

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

The formula you want is this array formula:

=INDEX($A$1:$A$5,MATCH(TRUE,COUNTIF($B$1:$B$5,$A$1:$A$5)>0,0))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of just Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

Avoid full column references with Array formulas as their calculations are exponential.

enter image description here

Upvotes: 1

Related Questions