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'? If
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 named
Dependents in which the column 1 entries all match a given employee
whose name was entered in a cell named Employee into cells X3:X#, you
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 has
multiple dependents with the same name.


---
Message posted from http://www.ExcelForum.com/