Receiving #N/A Error when using INDEX & MATCH Function

[chuh]

Excel 2007

I'm attempting to use the MATCH function nested within the function INDEX.
I'm receiving the error "#N/A" because my lookup_value in the MATCH function could not be found.

My lookup_value is referencing a cell (G7) that is a date.
This date is created using a formula.
The formula in G7 is: =sum(f7+1)
The end result of the formula in G7 is: 7/10/2011

Contents in F7: =sum(b5)
Contents in B5: 7/9/2011

When I change the contents of G7 to text "7/10/2011", I do not receive any "#N/A" errors and receive what is desired.

Expected Results:
I would like to be able to have the MATCH function reference a date that is created from a formula without having to copy and paste special values on each date. Any guidance would be appreciated.

Thanks

 

[barry houdini]

Hello chuh, welcome to MrExcel,

Can you post the INDEX/MATCH formula?

 

[Jonmo1]

Sounds like the dates in the range that match is searching are not real dates.

Post the actual index(match(..)) formula

 

[chuh]

Thanks for the quick response.

Here is the formula:
=INDEX(INDIRECT($B$3&"!$D$2:$HH$2000"),MATCH($C8,INDIRECT($B$3&"!$C:$C"),0), MATCH(G$7,INDIRECT($B$3&"!$D$1:$HH$1"),0))

You can also find a download link to the spreadsheet below:

https://docs.google.com/leaf?id=0B5Y9Atk-M8Y7MDA1MTk5ZmEtZjc2My00MTk2LWFmN2MtNjUyYzg3OWU0YTFj&hl=en_US

 

[Jonmo1]

Check the dates in D1:HH1 on the respective sheet named in B3.
Use
=ISNUMBER(D1) and Fill right to HH1
Are they TRUE or FALSE?

Also, your formula will not return the correct result as it is...
You're indexing D2:HH2000
But matching in C:C
So if the match is in say C3, the match returns 3
The 3rd position in D2:HH2000 is D4

You should change
$C:$C
to match the row#s in the Index Range.
$C$2:$C$2000

 

[chuh]

Thank You Jonmo1 for those corrections.

The dates from D1 to HH1 are returning as FALSE.

 

[chuh]

Ok. I think understand now.

When I manually enter the dates in D1:HH1, the dates become "numbers" and the index formula is working great.

Any recommendations on converting the dates to 'numbers'?

Thanks jonmo1

 

[Jonmo1]

Thank You Jonmo1 for those corrections.

The dates from D1 to HH1 are returning as FALSE.

Glad to help..

That's why the match isn't finding them.
They need to be entered as real dates.

Copy any blank cell
Highilght D1:HH1
Right click - Paste Special - Values - Add

That should convert them.

Also, your formulas on the Report page, don't need to be Array entered with CTRL + SHIFT + ENTER

 

[chuh]

Thanks Jonmo1, worked like a charm.