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.
--
TIA, Brad E.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brad E." wrote in message
...
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.
--
TIA, 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.
--
TIA, Brad E.


"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brad E." wrote in message
...
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.
--
TIA, Brad E.

.

One way...

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

--
Biff
Microsoft Excel MVP


"Brad E." wrote in message
...
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.
--
TIA, Brad E.


"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brad E." wrote in message
...
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.
--
TIA, 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" wrote:
One way...

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

Biff
Microsoft Excel MVP


"Brad E." wrote in message
...
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.
--
TIA, Brad E.


"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brad E." wrote in message
...
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.
--
TIA, Brad E.

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))

--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:
One way...

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

Biff
Microsoft Excel MVP


"Brad E." wrote in message
...
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.
--
TIA, Brad E.


"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brad E." wrote in message
...
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.
--
TIA, Brad E.