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

Question

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.

What happens if column 2 also had the value of 10? Are you expecting only one match? are you expecting the matching values to be comma separated? — Wyatt Shipman
– Wyatt Shipman, Commented Jun 17, 2016 at 17:16
Column 1 contains only numbers and there can only be at most one matching string from column 2 to column 1. Column two is fixed and no value gonna change — Navy
– Navy, Commented Jun 17, 2016 at 18:30

Scott Craner · Accepted Answer · 2016-06-17 18:46:12Z

1

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.

edited Jun 17, 2016 at 18:46

answered Jun 17, 2016 at 18:37

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Navy Over a year ago

Thanks a lot . I tried it and worked. I would look more into the formula to learn about it. Thanks a lot again! :-)

Navy Over a year ago

Can I get 'N/A' value when nothing matches up?

Wyatt Shipman Over a year ago

This seems to only work for me if the matching value is in B1, if not, it returns the last value in the list.

Navy Over a year ago

Does this works only for the given position of AA in column?

Scott Craner Over a year ago

@Navy see edit, I forgot to make the match look for exact. If this answers your question please mark it as correct by clicking the green check mark.

|

Collectives™ on Stack Overflow

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

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related