Vlookup to cope with duplicates

[KayeNightingale]

We have a list of Books; each Book has a Unique ISBN Number but,
unfortunately the same Book can be listed twice so we are struggling to write
a suitable VLOOKUP formula.

The VLOOKUP correctly finds the first occurence of an entry with that ISBN
number but how can we deal with finding the next occurence of the same ISBN
number?

There are 5000 rows of data and a lot of duplicates so this is causing a
huge amount of manual editing each Quarter.

Can anyone else please?
KayeNightingale

 

[Mike H]

Hi,

This ARRAY formula returns the Nth match. Put your lookup value in e1 and
the number of the match you want in F1 (i.e. 2 for the second match). See
below for how to enter an ARRAY formula.

=INDEX(B1:B20,LARGE((A1:A20=E1)*ROW(A1:A20),COUNTIF(A1:A20,E1)+1-F1))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[alexrs2k]

Hi.
You could use this conditional formatting formula to highlight the
duplicates on a column, in this case column A.
Go to Format->Conditional Formatting->Condition1, choose Formula Is, and
paste this formula:
=IF(COUNTIF($A:$A,A1)=1,FALSE,NOT(COUNTIF($A$1:A1,A1)=1))

 

[alexrs2k]

Remember to set the format to any color you wish.

 

[KayeNightingale]

Hi Mike

Thank you so much; I have just tried this on a dummy spreadsheet and it
works a treat! I'm off to test it on the real data.

Thanks again!
Regards
Kaye

 

[Mike H]

Glad I could help

Hi Mike

Thank you so much; I have just tried this on a dummy spreadsheet and it
works a treat! I'm off to test it on the real data.

Thanks again!
Regards
Kaye