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?€ƒ

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)

"ghost" wrote:

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?€ƒ

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



"ghost" wrote:

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?€ƒ