Vlookup with a difference

  • Thread starter Thread starter Leanne M (Aussie)
  • Start date Start date
L

Leanne M (Aussie)

Hi,

I am comfortable with standard vlookups but I have a situation that I think
vlookup can help with but I want a different answer brought back.

An my first sheet I have a list of codes that I know exist on the second
sheet, the problem is that there may be duplicates on the second sheet with
different information.

What I would like is if the code exists more than once I would like it to
flag this for me, if it does only exist once then show me the information in
specified column.

I hope I have explained this well enough but just let me know if you need
more info.

Thanks
 
Leanne,

A bit long winded but try this

=IF(ROWS(B$1:B1)<=COUNTIF(Sheet2!$A$1:$A$20,$C$1),INDEX(Sheet2!$B$1:$B$20,SMALL(IF(Sheet2!$A$1:$A$20=$C$1,ROW(Sheet2!$A$1:$A$20)-ROW($C$1)+1),ROWS(B$1:B1))),"")

This array entered (Ctrl+Shift+Enter)

It uses the value in C1 and looks this up in Column A of sheet 2 and if a
match is found returns Column B.

If you drag the formula down it will return the second and subsequent
matches. When no more matches are found it returns a null.

Mike
 
Back
Top