Getting a value from large table.

[Brad E.]

Hi, I have a data table where column A = Make (named MAKE), B = Model (named
MODEL), Row 1 = Model Year (named MODELYR).
On a different sheet, I have a list of miscellaneous combinations. On this
sheet, column A contains the make, B the Model and C the Model Year.
How can I get the contents from the table to be populated in column D for my
list?
I have tried SUMPRODUCT, SUMIFS and INDEX (with MATCH) functions, but
continue to get the #Value error. I have also array-entered each of the
functions.
I would like the value in D1 where MAKE=A1, MODEL=B1 and MODELYR=C1.

 

[Don Guillett]

sumproduct should work OK if you do it properly with the same length ranges,
etc. post your formula

 

[Brad E.]

I would like to use something like
=SUMPRODUCT(MAKE=A10,MODEL=B10,MODELYR=C10,Retail!$C$2:$G$247)
but
MAKE = Retail!$A$2:$A$247
MODEL = Retail!$B$2:$B$247
MODELYR = Retail!$C$1:$G$1 (consists of Model Years 2006 - 2010)
and, therefore, the ranges aren't the correct size.

 

[T. Valko]

One way...

=SUMPRODUCT(--(Make=A10),--(Model=B10),INDEX(Retail!C2:G247,,MATCH(C10,MODELYR,0)))

 

[Brad E.]

Thanks, Biff. Skipping the ROW entry in the INDEX function works great. I
didn't know you could do that.
Brad E.

 

[T. Valko]

When you index a 2D range you have to specify both the row and column
argument otherwise, you'll get an error.

In this application:

INDEX(Retail!C2:G247,,MATCH(C10,MODELYR,0))

The row argument has been omitted so it defaults to 0. Since there isn't a
relative row 0 in the range it includes *all* rows within the range.

You can either omit the row argument as I did or you can define it as 0:

INDEX(Retail!C2:G247,0,MATCH(C10,MODELYR,0))