OFFSET and MATCH functions for cells

[Neecy]

I have a spreadsheet and reference file in which I am attempting to import
the values of a cell from the reference file into the cell of the spreadsheet
template. For testing purposes, I setup the script below on the reference
file and tested.

=OFFSET($A$15,MATCH(P16,$C$16:$C$28,0),MATCH(Q16,$A$16:$A$28,0),MATCH(T16,$F$16:$F$28,0),1).

Most of the values imported over as should with the exception of a few that
showed #VALUE!
This is the cell/column values from the initial cell showed as followed:

CATEGORY CATEGORY PLANT
TO QUANTITY DATE FROM CODE BUILDER
RUB 4560 6/8/2009 ARB USA8 1
RUB 3154 6/8/2009 ARB USA8 2
ATB 203 6/8/2009 ARB USA8 2
RUB 4218 6/9/2009 ARB USA8 1
RUB 2660 6/9/2009 ARB USA8 2
ATB 106 6/9/2009 ARB USA8 2

This is the value from the cells I am trying to match showed as followed:
DATE CATEGORY CATEGORY
TO QUANTITY FROM BUILDER
6/8/2009 RUB 4560 ARB 1
6/8/2009 RUB 3154 ARB 2
6/8/2009 ATB 203 ARB 2
6/9/2009 RUB 4218 ARB 1
6/9/2009 RUB 2660 ARB 2
6/9/2009 ATB #VALUE! ARB 2

What am I doing wrong? Also, need to be able to use up to 6 MATCH functions
in each script and am only able to use 4 before I receive and error that says
too many arguements are used. From my understanding, I should be able to use
up to 7 MATCH functions in a script. Is this correct, and if so, please tell
me how.

Thanks-

 

[Bernie Deitrick]

Neecy,

Also, need to be able to use up to 6 MATCH functions
in each script and am only able to use 4 before I receive and error that says
too many arguements are used. From my understanding, I should be able to use
up to 7 MATCH functions in a script. Is this correct, and if so, please tell
me how.

You can use as many MATCH functions as you want, within the limits of formula length and the number
of arguments allowed by the parent function: Offset takes 5 arguments:

OFFSET(reference,rows,cols,height,width)

You will only be able to use 4 MATCH functions - since MATCH returns a number, it would only be
useful for rows, cols, height, and width.

The #VALUE! probably means that your third MATCH function is returning a number greater than 1, in
which case you would need to array enter your formula into multiple cells.

HTH,
Bernie
MS Excel MVP

 

[Neecy]

Thanks Bernie-
How do I array enter my formula into multiple cells?

Neecy

 

[Neecy]

Thanks Bernie-

How do I array enter my formula into multiple cells?

 

[Bernie Deitrick]

Neecy,

Select multiple cells - say, 2 or 3 or 4 cells within one column - and then
to enter your formula press Ctrl-Shift-Enter instead of just Enter. If you
do it correctly, Excel will enclose your formula inside curly braces { },
and you will not be able to edit just one cell - you need to edit all four
cells at once.

HTH,
Bernie
MS Excel MVP