Complicated. Not sure it can be done. multiple match/lookups

**sricha89** · 11-07-2012, 11:11 PM

I have been trying to figure this out all night and I am not sure it is even possible. I have attached the file.

I have 3 worksheets.
My first worksheet is the data and my second 2 sheets need populated from the data.
The first sheet starts with a row of names (first column left blank).
The second row has 2 columns under each name one being CC and the other being CL.
In column A which was left blank for the first 2 rows there is a list of dates.
The data is then filled with data that correlates with the other 2 worksheets.

The second worksheet corresponds to the CC data.
The worksheet has that same list of names vertically down the first column (leaving the first row blank).
The first row (leaving the first column blank) has the numbers 1-10. 1 being in B and 10 being in K.
So what needs done is the name in column A needs found on the first sheet then the formula needs to look at the CC column for each name is filled with the numbers 1-10 or is left blank. If the number is in the column the date the number was entered needs put in the CC sheet for that person.

The third worksheet corresponds to the CL data. This is the more complicated of the two.
The first row has numbers 1-4. The second row has letter combinations in columns under the numbers.
Under "1" is "A","S","GR","T". Under "2" is "S","GR","GE". Under "3" is "S","GR","GE". Under "4" is "TI","TM","S".
In the first column starting in the third row is the same list of names.
So what needs done is the name in Column A needs found on the first sheet then the formula needs to take the column from row 1 and the Letter(s) from Row 2 and match them in the CL column on the first page under the name. On the first page they will be written as "1A","1S"... The date the Number/Letter was entered on the first page needs filled in on the CL worksheet.

I have tried so many things and I am at a lost with even filling the CC data.

**FDibbins** · 11-07-2012, 11:38 PM

It might be easier to help you if you manually added a few answers, so we can see what you're expected outcome is?

**sricha89** · 11-07-2012, 11:46 PM

CC!H2 should be sheet1!A3 (Stacey completed her 7th CC on 11/14/2012)
CL!C3 should be sheet1!A4 (Stacey completed her 1S CL on 11/15/2012)
CC!D3 should be sheet1!A5 (John completed his 3rd CC on 11/16/2012)
CL!G4 should be sheet1!A5 (John completed his 2GR CL on 11/16/2012)
CC!J4 should be sheet1!A8 (Sara completed her 9 CC on 11/19/2012)
CL!K5 should be sheet1!A7 (Sara completed her 3GE on 11/18/2012)