VLOOKUP with a difference

  • Thread starter Thread starter Anthony Slater
  • Start date Start date
A

Anthony Slater

Hi

I would like some help with the following: -

This my scenario:

a anthony
a brian
a silvan
a john
b fred
b peter
b michael

If I apply a VLOOKUP to search for "a" the corresponding
name returned will be that of "Anthony". Is it possible to
return the second match for the letter "a" that
is "Brian". Likewise, the third match for letter "b" would
show "Michael"

Looking forward to your rely
 
Hi Anthony,
If I apply a VLOOKUP to search for "a" the corresponding
name returned will be that of "Anthony". Is it possible to
return the second match for the letter "a" that
is "Brian". Likewise, the third match for letter "b" would
show "Michael"
Click to expand...

You may use this array formula:

=INDEX(B1:B7,SMALL(IF(A1:A7="a",ROW(B1:B7),""),3))

(press control-shift-enter to put this in a cell)

The 3 at the end tells it to get the third match.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
Thanks but what I really want is for all the corresponding
names to be listed that are associated with "a"

i.e. If I look up "a", then all names associated with "a"
are listed
 
Hi Anthony,
Thanks but what I really want is for all the corresponding
names to be listed that are associated with "a"

i.e. If I look up "a", then all names associated with "a"
are listed
Click to expand...

So how about using Data, Filter, Autofilter?

Alternatively, you could use my formula, adapted like this:

=INDEX(B1:B7,SMALL(IF(A1:A7="a",ROW(B1:B7),""),ROW()))

But enter like this:

- select the number of cells you think corresponds to the number of
occurrences, starting from row 1.
- type the formula and hit control-shift-enter.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
Anthony said:
Thanks but what I really want is for all the corresponding
names to be listed that are associated with "a"

i.e. If I look up "a", then all names associated with "a"
are listed
Click to expand...

A couple of other approaches are available if the functions in the
freely downloadable file at http://home.pacbell.net/beban are available
to your workbook; array enter into a column of cells large enough to
accommodate the output:

=VLookups("a",A1:B7,2) or

=INDEX(ArrayRowFilter1(A1:B7,1,"a"),0,2)

Alan Beban
 
Anthony Slater said:
Thanks but what I really want is for all the corresponding
names to be listed that are associated with "a"

i.e. If I look up "a", then all names associated with "a"
are listed
Click to expand...

If this is one-off, filtering would be a better idea. If you need formulas,
this could be done as follows.

Assuming your sample data is in A1:B7, your entry 'a' is in E1 and the
topmost result in F1. Enter the following formulas.

F1:
=VLOOKUP(E1,A1:B7,2,0)

F2 [*array* formula]:
=INDEX($B$1:$B$7,MATCH(1,($A$1:$A$7=$E$1)
*(COUNTIF(F$1:F1,$B$1:$B$7)=0),0))

Array formula means you have to hold down [Ctrl] and [Shift] keys before
pressing [Enter] to enter the formula. Select F2 and fill down as far as
needed.
 
Back
Top