vlookup for multiple entries

[michele]

Is it possible to have a vlookup find all instances of a
number rather than just the first one? Thanks for your
help.

 

[Paul]

Is it possible to have a vlookup find all instances of a
number rather than just the first one? Thanks for your
help.

You need a separate formula for each (that is, the first, second, third....
instances). Look here for details in the paragraph "Arbitrary Lookups":
http://www.cpearson.com/excel/lookups.htm

If you want to do something other than list the separate instances, for
example to count or sum them, there are other ways. SUMPRODUCT is a useful
function here. Post back if you want more specific details.

 

[Harald Staff]

Vlookup finds a match in a table and returns something from the matching row
into the cell. What should it return with multiple matches ?

Perhaps Sumif and Countif is what you want ?

 

[Peo Sjoblom]

No, not a vlookup. There are some options using index and some other
functions to return first, second occurrence etc. It's a bit complicated so
I would
recommend using something simpler like data>autofilter. As an example, a
vlookup
formula that looks like this

=VLOOKUP(A1,B2:C30,2,FALSE

a formula that would pick up first second etc looks like this

=INDEX($C$1:$C$30,SMALL(IF(($B$2:$B$30=$A$1),ROW($B$2:$B$30)),ROW(1:1)))

entered with ctrl + shift & enter

this formula has to be copied down

when there are no more occurrences it will return a num error

 

[Alan Beban]

If you're interested in downloading the freely downloadable file from
http://home.pacbell.net/beban, the VLookups function is designed to do this.

Alan Beban