Lookup with multiple sources

**saved1ne** · 01-21-2011, 10:20 AM

I have a spreadsheet that provides data to separate tabs (data comes from Access) , each tab has a unique name which is used as the identifier and contains data and a date. I tried using vlookup/match but the problem is that the raw data includes repeating dates for different identifiers.

What I need is something that can look for an identifier and a date. What match does now is it finds the first date it is looking for and stops, I need it to find the date associated with the identifier. (I hope this makes sense.)

**NBVC** · 01-21-2011, 10:38 AM

Try something like:

=INDEX(D3:D17,MATCH(1,INDEX((A3:A17=A1)*(C3:C17=B1),0),0))

where A3:A17 contains the dates or Id's to match up to your input in A1 and C3:C17 contains the other match up field to input in B1 and C3:C17 contains the field you want to extract.

**saved1ne** · 01-21-2011, 12:08 PM

OK, first I apologize for such a poorly written question. I see what you are doing and I think I need to better explain what I am doing to get to an answer.

My data sheet has 4 columns. A = ID, B = Date, C = data 1, D = data 2

My other sheets are named after each ID.

My ID sheet has the ID in cell A1, the monthly date range starts in cell A12 (1-1-2007) and goes until current. I need to look up on the data sheet the (data 1) information that corresponds to the combination of cells A1 and A12, A13, A14.........until the data for all dates is filled in.

I used what you gave me and got an #N/A so I think it just needs to be fine tuned.

**NBVC** · 01-21-2011, 12:15 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**saved1ne** · 01-21-2011, 01:01 PM

Here is the sample you asked for. I included the tabs to show what the finished tabs should look like.

**NBVC** · 01-21-2011, 01:13 PM

In B12 of each sheet try:

=SUMPRODUCT(--(Data!$A$2:$A$95=$A$1),--(Data!$B$2:$B$95=$A12),Data!C$2:C$95)

adjust ranges to suit... keep them to a minimum, then copy down and over to next column.