Complicated lookup/match formula help needed!

[Jason]

Hi--

I have a sheet called "ClientList" where column A has a list of ID
numbers. In another sheet called "Clinicians", these ID numbers are
in columns BA:BJ. There is other information (for example # visits
and # individual) associated with each client's ID in columns BK and
on.

I am trying to write a formula to match each ID number in ClientList
with the same ID number in the Clinicians spreadsheet, and then return
information in columns BK and on. The number of columns for visits
and individual can be stored in a separate cell, say Z1. For example,
the Clinicians sheet looks like this (data starts in row 3):

|------------------visits--------| |-----------Individual-----------|
BA BB BC BD BE BF BG BH BI BJ BK BL BM BN BO BP BQ BR
BS BT BU BV
4-2 3-2 1-9 2-2 3-3 4-5 5 4 3
2 1 11 1 2 0 1 3 4
5-1 1-1 1-11 8 1
0 0 1 0
7-7
3 2
6-4 3-9 4
10 1 10


I would LIKE the ClientList sheet to look like this (column A is
already filled with the ID numbers, I just need to match the other
fields):
VISITS INDIVIDUAL
A B C
4-2 5 1
3-2 4 2
1-9 3 0
2-2 2 1
3-3 1 3
4-5 11 4
5-1 8 0
1-1 1 1
....

Thanks for any suggestions!

Jason

 

[Jason]

I see that my formatting got messed up on the post. This should be
columns BA:BJ for the ID numbers (with a dash), columns BK:BP for
#visits, and columns BQ:BV for #Individual. There are 6 columns for
visits and 6 for individual because at most the 6th column of ID#s has
a number entered.

I'd really appreciate any help with this!

Thanks,
Jason

 

[Herbert Seidenberg]

Excel 2003
With Sumproduct and Lists.
http://www.freefilehosting.net/download/3dldh