using Vlookup to find multiple results

[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?

 

[Frank Kabel]

Hi
though possible with array formulas I would use Data -
Filter for this kind of application.

 

[Guest]

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

 

[hgrove]

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. . . .

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,

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