How to find duplicate cells in Column a & retrieve column B

G

ghost

How to find duplicate cells in Column a & retrieve column B
A B C D E
1 abc cba =vlookup(c1,a1:a4,1,false) 2
2 cba………………………………………………

What I want to do is retrieve the A value in E according to the value in D1? 
 
A

Alojz

Hi, I assume u want to display 2 in E1 as u r looking for cba in column B,
right?
Formula u need to insert in E1 may be: =SUM((C1=B1:B2)*A1:A2), then press
ctrl+shift+enter (array formula)
 
X

xlmate

Vlookup doesn't look up value to the left of the table.
Assume that you have no header row and data start at that row onward.
Further assume that you place the lookup value in D1 and not C1

Try this non array formula, copy and paste the formula to your target cell
in E1
=INDEX(A1:A10,MATCH(D1,B2:B10,0))

if you want to ignore #N/A when the lookup value does not exist

use this to return a msg - "Non-Exist"

=IF(ISNA(INDEX(A1:A10,MATCH(D1,B1:B10,0))),"Non-Exist",INDEX(A1:A10,MATCH(D1,B1:B10,0)))


--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you.


Thank You

cheers, francis
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top