Index/Match or VLOOKUP? Need some help.

**Mark7** · 12-29-2011, 01:33 PM

I am tracking data in a sheet named "Data". The sheet is constructed so that I am running week dates for 2012 in Row 1 (from Jan 1 - Dec 31). Below this, I have a series of items I am tracking in Column A (you could say A1 contains my RowHeaders). I have unique headers from A3:A10. Starting Jan 2, I will begin inputting results into the appropriate date's columns from B3:B10, on Jan 3 it will be C3:C10, and so forth.

I have a few sheets similar to "Data". What I am attempting to do is create a main sheet which I am calling "Daily". My goal for this sheet is to pull my results off of each of my data tracking sheets and assemble them based on the date that I select.

So, from within sheet "Daily" I have tried to use INDEX/MATCH functions to search my date range, select a specific date, then return the numeric value found in sheet name "Data", row "Apples" for that corresponding date. I know this is possible, i'm not sure whether I should keep messing with INDEX/MATCH function, research VLOOKUP's, or if I need to use something entirely different. My end goal is to be able to enter Jan 3's results into my "Data" sheet, then be able to go to the "Daily" worksheet, and be able to adjust my functions so that I can tell it to pull Jan 3's results from "Data" rather than Jan 2 which I had used the previous day.

Thanks for your help!

This is a snippet of what I have within my sheet titled "Data":

"Data" 2-Jan-12 3-Jan-12 4-Jan-12 5-Jan-12 6-Jan-12 Week of 1/2-1/6

Apples 250 125 30 75 200 680

This is what I have in my sheet titled "Daily", it is where I wish to return the values located within sheet "Data" for each of the corresponding fields:

Apples Bananas Cats Dogs Eggs Fish Granola Hops
0 0 0 0 0 0 0 0

**Haseeb A** · 12-29-2011, 03:06 PM

Hello Mark,

Assume on 'Daily' sheet A1 is the Date you are choosing. A2 to across is the datas, Apples, Bananas etc.. So in A3, try this,

=SUMIF(Data!$A:$A,A$2,INDEX(Data!$A:$NZ,0,MATCH($A$1,Data!$1:$1,0)))

copy across.

**Mark7** · 12-29-2011, 03:13 PM

Haseeb, that is perfect! Exactly what I was trying to do. Would you mind walking through the formula for me and explaining what each part is doing? I need to apply this to a few other sheets and the data isn't necessarily constructed the same, but I'd like the functionality to work because this is exactly what I was hoping to create.

Thanks!

Mark