using Vlookup to find multiple results

  • Thread starter Thread starter Kent
  • Start date Start date
K

Kent

I'm designing a form to lookup and select data from a
large database where individuals may have multiple
entries in the same column. Vlookup and/or index/match
will seem to find either the first or the last but how
can you find the other entries where there are more than
two?
 
Hi
though possible with array formulas I would use Data -
Filter for this kind of application.
 
Thanks, the problem is I'm using a variable data field in
a form. Thus, I'm selecting an employee, then looking up
various fields for kinds of benefits, beneficiaries,
amounts etc. In one case I need to display the
dependents, and there could be none or there could be as
many as four or five. Because I have over 300
individuals, I have to list the dependents in a column,
and so I can't nest vlookup's to find separate entries.
Does this make sense?

Kent
 
Anonymous wrote...
Thanks, the problem is I'm using a variable data field in a form.
Thus, I'm selecting an employee, then looking up various fields
for kinds of benefits, beneficiaries, amounts etc. In one case I
need to display the dependents, and there could be none or
there could be as many as four or five. Because I have over 300
individuals, I have to list the dependents in a column, and so I
can't nest vlookup's to find separate entries. . . .
Click to expand...

Unclear.

Do you mean a 'form' implemented in a worksheet or a VBA 'Userform'? I
the former, then what do you mean by "I
can't nest vlookup's to find separate entries."?

If you want to return many entries from column 2, say, of a table name
Dependents in which the column 1 entries all match a given employe
whose name was entered in a cell named Employee into cells X3:X#, yo
could use the following formulas.

X3:
=VLOOKUP(Employee,Dependents,2,0)

X4 [array formula]:
=IF(COUNTIF(INDEX(Dependents,0,1),Employee)
COUNTA(X$3:X3),INDEX(Dependents,
Click to expand...
MATCH(1,(INDEX(Dependents,0,1)=Employee)
*(COUNTIF(X$3:X3,INDEX(Dependents,0,2))=0),0),2),"")

Select X4 and fill down as needed. This assumes no employee ha
multiple dependents with the same name
 
Back
Top