MATCH function problem

**LACA** · 05-20-2006, 01:17 PM

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?

**davesexcel** · 05-20-2006, 01:34 PM

I don't know if the function is correct, but is this an array formula??
Ctrl,shift,enter if it is...

**LACA** · 05-20-2006, 01:45 PM

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

**davesexcel** · 05-20-2006, 02:17 PM

ok I think I have it

=INDEX(Sheet2!A3:F3,MATCH(1,(Sheet2!A1:F1=Sheet1!A1)*(Sheet2!A2:F2=Sheet1!A2),0))

this is an array formula so after you enter the formula in a cell you will be required to confirm it by pressing

Ctrl Shift Enter

at the same time

Biff · 05-20-2006, 02:20 PM

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

**LACA** · 05-20-2006, 02:27 PM

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?

Biff · 05-20-2006, 02:50 PM

"LACA" <[email protected]> wrote in message
news:[email protected]...
>
> 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

**LACA** · 05-20-2006, 08:43 PM

Another question:

Suppose I have the same scenario, but this time with a Sheet3 that is set up exactly like Sheet2, but with different data for another company.

How do I set up my formula, which currently is

=MATCH(A1&A2,Sheet2!A1:F1&Sheet2!A2:F2,0)

to incorporate the Sheet3 data (i.e., Sheet1 would be a consolidated summary of the supporting sheets, and not subtotaled by company).

Is there a way to combine my arrays from various other worksheets? I may have as many as 10 different company sheets, each with different data that needs to be rolled up into one.

Thanks.

Biff · 05-20-2006, 10:10 PM

Not sure I understand what you mean.

If you have other sheets that you need the same type of data from then you
just have to use that specific sheet name in another formula:

> =MATCH(A1&A2,Sheet2!A1:F1&Sheet2!A2:F2,0)

=MATCH(A1&A2,Sheet3!A1:F1&Sheet3!A2:F2,0)

Are your sheet names really Sheet1, Sheet2, Sheet3 etc?

You can enter 1 formula and craft it in such a way as to have the sheet
names automatically increment as you copy the formula but that all depends
on the real sheet name.

Biff

"LACA" <[email protected]> wrote in message
news:[email protected]...
>
> Another question:
>
> Suppose I have the same scenario, but this time with a Sheet3 that is
> set up exactly like Sheet2, but with different data for another
> company.
>
> How do I set up my formula, which currently is
>
> =MATCH(A1&A2,Sheet2!A1:F1&Sheet2!A2:F2,0)
>
> to incorporate the Sheet3 data (i.e., Sheet1 would be a consolidated
> summary of the supporting sheets, and not subtotaled by company).
>
> Is there a way to combine my arrays from various other worksheets? I
> may have as many as 10 different company sheets, each with different
> data that needs to be rolled up into one.
>
> Thanks.
>
>
> --
> LACA
> ------------------------------------------------------------------------
> LACA's Profile:
> http://www.excelforum.com/member.php...o&userid=30381
> View this thread: http://www.excelforum.com/showthread...hreadid=543982
>

**LACA** · 05-21-2006, 12:51 AM

Sorry for not being clear.....

My sheet names are actually city names from which I am collecting sales data, both forecasted and actual. My full formula on Sheet1 ("Consolidated"), is actually this:

=INDEX(Boston!$C9:$O9,,MATCH($C$5&$C$6,Boston!$C$6:$O$6&Boston!$C$1:$O$1,0))

Now suppose that I have an additional tab labeled "Chicago". It is identical in format to "Boston" but contains different sales data.
I want to rewrite my formula above on the Consolidated tab to roll up the data on the Boston and Chicago tabs into one total (i.e., Boston and Chicago sales data combined).
I'm not sure how to combine the arrays from both the Boston and Chicago tabs to make it work.
Hope that makes sense....

Biff · 05-21-2006, 08:45 PM

If I understand you......

> =INDEX(Boston!$C9:$O9,,MATCH($C$5&$C$6,Boston!$C$6:$O$6&Boston!$C$1:$O$1,0))

That formula returns a numeric value that you want to add together with the
same type of data from sheetname Chicago. If that's the case you'd need to
do something like this:

=INDEX(Boston!$C9:$O9,,MATCH($C$5&$C$6,Boston!$C$6:$O$6&Boston!$C$1:$O$1,0))+INDEX(Chicago!$C9:$O9,,MATCH($C$5&$C$6,Chicago!$C$6:$O$6&Chicago!$C$1:$O$1,0))

But I feel like I'm not getting this!

Biff

"LACA" <[email protected]> wrote in message
news:[email protected]...
>
> Sorry for not being clear.....
>
> My sheet names are actually city names from which I am collecting sales
> data, both forecasted and actual. My full formula on Sheet1
> ("Consolidated"), is actually this:
>
> =INDEX(Boston!$C9:$O9,,MATCH($C$5&$C$6,Boston!$C$6:$O$6&Boston!$C$1:$O$1,0))
>
> Now suppose that I have an additional tab labeled "Chicago". It is
> identical in format to "Boston" but contains different sales data.
> I want to rewrite my formula above on the Consolidated tab to roll up
> the data on the Boston and Chicago tabs into one total (i.e., Boston
> and Chicago sales data combined).
> I'm not sure how to combine the arrays from both the Boston and Chicago
> tabs to make it work.
> Hope that makes sense....
>
>
> --
> LACA
> ------------------------------------------------------------------------
> LACA's Profile:
> http://www.excelforum.com/member.php...o&userid=30381
> View this thread: http://www.excelforum.com/showthread...hreadid=543982
>