Vlookup to cope with duplicates

  • Thread starter Thread starter KayeNightingale
  • Start date Start date
K

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
 
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
 
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))
 
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
 
Glad I could help

KayeNightingale said:
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
Click to expand...
 
Back
Top