vlookup for multiple entries

  • Thread starter Thread starter michele
  • Start date Start date
M

michele

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

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.
 
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 ?
 
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
 
Back
Top