I dont know the code but this may be a workout....enter this formula in B1
and drag it all the way down..say till B50. then select C1 go to DATA |
VALIDATION | select LIST and then enter B1:B50 in the box. Press OK. Now
Hide column B.

Does that do what you want?


"Leslie" wrote in message
...
I tried your formula, but it did not let me choose any record. It polulate
my cell with the first entity that matched my claim number.

Is it possible to code like a combo box in my cell with only the records
that match my claim number?

"Gaurav" wrote:

Assuming you are entering the claim number in Sheet1 cell A1.

In Sheet2, claim numbers in A1:A100 and names in B1:B100

enter this formula in Sheet1 B1

=IF(ROWS(A$1:A1)<=COUNTIF(Sheet2!A$1:A$100,A$1),IN DEX(Sheet2!B$1:B$100,SMALL(IF(Sheet2!A$1:A$100=A$1 ,ROW(Sheet2!B$1:B$100)-MIN(ROW(Sheet2!B$B:B$100))+1),ROWS(A$2:A2))),"")

Press CTRL+SHIFT+ENTER and not just ENTER. Drag it down according to the
maximum number of possible Entities.

This should help.


"Leslie" wrote in message
...
I have two worksheets in a single workbook. My fisrt worksheet contains
claim numbers and my second worksheet contains a link file listing all
claim
entities and sorted by claim number. There are multiple claim entities
per
claim. I am trying to figure out how I can use the vlookup to find all
the
entities for a particular claim and then allow the user to select from
the
vlookup which one they want to use.

worksheet 1
CLAIM NO
104068

worksheet 2
CLAIM NO ENTITY
2387 Smith, John
183778 All State
183778 Johnson, Alisa
104068 Farm Bureau
104068 Goodloe, Milford
104068 Thompson, Nancy

Using the above example, when the user enters 104068 on the first
worksheet,
I need a function in the next cell to possibly show the user all the
entities
from worksheet 2 that match that claim number and then let the user
decide
which one of the entities to pull in.