MATCH function problem

[LACA]

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]

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

 

[LACA]

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

 

[Biff]

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

 

[davesexcel]

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

 

[LACA]

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]

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]

Another question:

Suppose I have the same scenario, but this time with a Sheet3 that i
set up exactly like Sheet2, but with different data for anothe
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 consolidate
summary of the supporting sheets, and not subtotaled by company).

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

Thanks

 

[Biff]

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]

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]

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