INDEX MATCH with INDIRECTs

Hello,

I've been struggling with getting a complex (to me!) formula working. I have sheets named for each month (SEP, OCT, NOV, etc) Each has the day of the month horizontally, and a series of locations vertically. Every day, I go to the right date and enter some info next to each location, based upon date. I also have a sheet called TODAY:

Cell A3 has =TODAY()
Cell C1 has =TEXT(A3,"MMM")
Cell D1 has =TEXT(A3,"DD")

My hope is to be able to use the date in A3 to go to the right sheet and the right day, and grab the stuff I typed into the locations cells.

I have been trying to start easy, by hardcoding the month into it and that works, but I am struggling with getting it to work with an INDIRECT.

=INDEX(SEP!$B$3:$B$71,MATCH(Today!A5,SEP!$A$3:$A$71,0))

Any help with this would be most appreciated!

CP

=INDeX(INDIRECT($C$ 1& "!$B$3:$B$71"), MATCH(INDIRECT(A3 & "!A5), INDIRECT(C1 & "!$A$3:$A$71"),0)

Hi ChemistB, thank you for the attempt, but I could not get it to work. But with a clearer head and more caffeine, I have gotten a little farther with it. This works:

=INDEX(INDIRECT(""&$C$2&"!$B$3:$B$71"),MATCH(Today!A5,INDIRECT(""&$C$2&"!$A$3:$A$71"),0))

But does not yet do what I am hoping.. The next part would be to get it to look at the day, found in cell D1, and use an additional MATCH to get the data. I tried this, but it returns an #N/A:

=INDEX(INDIRECT(""&$C$2&"!$B$3:$B$71"),MATCH(Today!A5,INDIRECT(""&$C$2&"!$A$3:$A$71"),0),MATCH(Today!D1,SEP!B2:AF2,0))

I will continue beating my head against the Excel wall, but if someone could possibly show me where I am going wrong, that would rock.

Thanks!

EDIT

Not sure if this makes a difference, but when I go to Cell D1 and change it from =TEXT(A3,"DD") to 11, the #N/A changes to a #REF...

can you upload an example workbook?

Okay,
1. Need to change D1 to a number (since the headers in SEP are numbers)

=TEXT(A3,"DD")+0

2. In G5, you don't need the single quotes ' since your sheet names never have blanks. So remove them. Also, the original lookup range needs to be $B$3:$AF$71 So G5 becomes

=INDEX(INDIRECT($C$2&"!$B$3:$AF$71"),MATCH(Today!A5, INDIRECT($C$2&"!$A$3:$A$71"),0),MATCH(Today!D1,SEP!$B$2:$AF$2,0))

Does that help?

Originally Posted by cpyter

=INDEX(INDIRECT($C$2&"!$B$3:$AF$71"),MATCH(Today!A6, INDIRECT($C$2&"!$A$3:$A$71"),0),MATCH(Today!D$1,INDIRECT($C$2&"!$B$2:$AF$2"),0))

Quoted range references within the INDIRECT function are TEXT strings and as such they will never change. So, there's no need for the dollar signs $:

=INDEX(INDIRECT($C$2&"!B3:AF71"),MATCH(Today!A6,INDIRECT($C$2&"!A3:A71"),0),MATCH(Today!D$1,INDIRECT($C$2&"!B2:AF2"),0))

Saves a few keystrokes and makes it a bit easier to read!

One last issue I just noticed... When the cell in the month sheets is blank, the cell in the Today tab pulls a zero, rather than an empty. Is there an easy way to fix this?

What type of data does the formula normally return? Is it text? Numbers? Could be both? Something else?

If it's numbers, is 0 an otherwise valid result?

OK, that means you basically have to repeat the formula...

=IF(the result of the formula=blank, return a blank, repeat the formula here)

And that would look like this...

=IF(INDEX(INDIRECT($C$2&"!B3:AF71"),MATCH(Today!A6,INDIRECT($C$2&"!A3:A71"),0),MATCH(Today!D$1,INDIRECT($C$2&"!B2:AF2"),0))="","",INDEX(INDIRECT($C$2&"!B3:AF71"),MATCH(Today!A6,INDIRECT($C$2&"!A3:A71"),0),MATCH(Today!D$1,INDIRECT($C$2&"!B2:AF2"),0)))