how to match an array of 1 row, to its counterpart in a larger arr

[Guest]

what formulas to use: to match an array of one row and 3 columns (cells) to
its exact counterpart within a template array of 144 rows (and 4 columns). It
should then return the value in the fourth column of the template array when
its 3 columns have been matched.

One excel array contains 100,000 rows each with 3 parameters: color(3
kinds), shape (3 kinds), and diameter grouping (16 levels). Obviously there
are multiples . The template array contains all the different combinations
equalling 144 rows of unique: color, shape, and diameter grouping. The fourth
column in each template row is the price associated with that unique
combination of color, shape, and diameter grouping.

 

[Frank Kabel]

Hi
try something like the following array formula (entered with
cTRL+SHIFT+ENTER):
=INDEX(D1144,MATCH(1,(A1:A144=X1)*(B1:B144=Y1)*(C1:C144=Z1),0))

 

[Guest]

Hi Frank,

Danke

You obviously know much more that I do. It worked like a charm.
I wanted to "fix" the range of "D", "A", "B", and "C" with the "$" sign..but
it didn't want to let me....didn't want to recognize the "$" symbol...for
some reason...
Any thoughts.....

other than that....thankyou very much...

 

[Frank Kabel]

Hi
=INDEX($D$1:$D$144,MATCH(1,($A$1:$A$144=X1)*($B$1:$B$144=Y1)*($C$1:$C$1
44=Z1),0))

 

[Guest]

I just forgot to hold down the cntrl + shift + enter when using the $ to fix
the ranges....oooppss..

thanks again...you've saved me hours....