Made a simple template but not working

**BNCOXUK** · 04-13-2013, 10:25 AM

Hi all,

In Sheet2, I named cell A1 to Text_1, and cell A2 to Text_2, and then make them with values of Sheet1!A:A, Sheet1!B:B.

I then used the formula: =INDEX(Text_2,MATCH(DATEVALUE("2012-Jun-30"),Text_1,0)) to find the value I want in Sheet1!B:B. But this resulted in an #VALUE! error.

Do you know why this is wrong? I attached the file here. Thanks for help.

**davesexcel** · 04-13-2013, 11:02 AM

Possibly

=INDEX(Text2,MATCH(DATEVALUE("3/31/2012"),Text1,0))

The range was wrong in the formula.
You may have to change the date back to your region.
Hit Ctrl & F3 and see that the Text_1 and Text_2 still refer to A1 And A2 on sheet 2 not the columns you thought they refered to.

http://www.davesexcel.com/howtouseindexmatch.htm

**BNCOXUK** · 04-13-2013, 06:42 PM

Thanks for the help.

Is it possible that I can make a formula in cells A1, A2 whose values refer to Sheet1!A:A and Sheet1!B:B)? And then I use the formula: =INDEX(Text_2,MATCH(DATEVALUE("2012-Jun-30"),Text_1,0)) to find the value I want in Sheet1!B:B. Here, cells A1, A2 are named as Text_1 and Text_2.

I know named ranges can accomplish this, but is it possible to do this without named ranges?