Vlookup to cope with duplicates
 

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),COUNTI F(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

"KayeNightingale" wrote:

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

Vlookup to cope with duplicates
 

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" wrote:

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),COUNTI F(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

"KayeNightingale" wrote:

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

Vlookup to cope with duplicates
 

Glad I could help

"KayeNightingale" wrote:

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" wrote:

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),COUNTI F(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

"KayeNightingale" wrote:

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