MATCH function problem

Help!

I am creating a large lookup formula but am having trouble writing one piece of it using MATCH.

Sheet1 has the following:

Cells A1 through C1 = "Week 1", "Week 2", "Week 3" respectively.
Cells A2 through C2 = Manual input area; Options are either "F" (forecast) or "A" (actual)

Sheet2 has the following:

Cells A1 through C1 = "Week 1", "Week 2", "Week 3" respectively
Cells A2 through C2 = "F" in each cell

Cells D1 through F1 = "Week 1", "Week 2", "Week 3" respectively
Cells D2 through F2 = "A" in each cell

In Sheet1, I want to reference on row 3 the position of the data I enter on row 2.

Example: In Sheet1, I enter an "F" in A2.
My formula on cell A3 is: =MATCH(A1&A2,Sheet2!A1:F1&Sheet2!A2:F2,0)

Desired result is 1
If I enter "A" instead of "F", desired result is 4

Instead I get #VALUE!

Where am I going wrong?

Sorry, I'm not understanding your response.....

Hi!

Select the cell that holds this formula.

Press function key F2. That will put you in Edit mode.

Hold down both the CTRL key and the SHIFT key then hit ENTER.

The formula is an array formula. It MUST be entered using the key
combination of CTRL,SHIFT,ENTER not just ENTER.

If done proerly Excel will enclose the formula in squiggly braces { }. You
cannot just type these braces in. You MUST use the key combination. Also, if
you edit an array formula it MUST be re-entered as an array using the key
combo.

Biff

"LACA" <LACA.284alb_1148147401.7074@excelforum-nospam.com> wrote in message
news:LACA.284alb_1148147401.7074@excelforum-nospam.com...
>
> Sorry, I'm not understanding your response.....
>
>
> --
> LACA
> ------------------------------------------------------------------------
> LACA's Profile:
> http://www.excelforum.com/member.php...o&userid=30381
> View this thread: http://www.excelforum.com/showthread...hreadid=543982
>

Thanks for the responses!

I used my current formula and got it to work once I did the Edit-Ctrl-Shift-Enter thing.

So once I do that, and assuming that I do not ever have to edit my formulas, I can now change my input fields at any time and the result will automatically change, as it seems to now be doing, yes?

"LACA" <LACA.284cfy_1148149800.7533@excelforum-nospam.com> wrote in message
news:LACA.284cfy_1148149800.7533@excelforum-nospam.com...
>
> Thanks for the responses!
>
> I used my current formula and got it to work once I did the
> Edit-Ctrl-Shift-Enter thing.
>
> So once I do that, and assuming that I do not ever have to edit my
> formulas, I can now change my input fields at any time and the result
> will automatically change, as it seems to now be doing, yes?

Yes!

Biff