Vlookups

  • Thread starter Thread starter Wannabe Excel Guru but failing miserably
  • Start date Start date
W

Wannabe Excel Guru but failing miserably

Hi! Can vlookup (with false - i.e. exact match) find the
second (or third, or forth, etc...) exact match rather
than just the first?

Thanks!
 
Hi! Can vlookup (with false - i.e. exact match) find the
second (or third, or forth, etc...) exact match rather
than just the first?
Click to expand...

No. If the original VLOOKUP formula were

=VLOOKUP(x,Table,n,FALSE)

one possibility that uses only built-in functions would be the array formula

=INDEX(Table,SMALL(IF(INDEX(Table,0,1)=x,
ROW(Table)-CELL("Row",Table)+1),k),n)

which would find the k_th match, if there were at least k matches.
 
If the functions in the freely downloadable file at
http://home.pacbell.net/beban were available to your workbook:

=VLookups(x, Table, n) array entered into a column of at least enough
rows to accommodate the number of occurrences of x in the left hand
column of the table (where n is the number of the column containing the
matching value).

Alan Beban
 
Back
Top