Multiple conditions in MATCH and INDEX functions

[clanofmiller]

Hi, I am too dumb to work this out on my own.

I have an array of given values of:

TypeA Ver1 Price
TypeA Ver2 Price
TypeA Ver3 Price
TypeB Ver1 Price
TypeC Ver3 Price

What I need to do is to create a function, which would fill value
Price from Sheet2 into Sheet1 into similar form to this one:

ConA ConB Price --> Header row
TypeA Ver2 Price --> Value row where TypeA and Ver2 are inserted
manually and the function in field Price would look up the Price where
TypeA and Ver2 have identical MATCH results (if it can be done with
MATCH)

I have been reading about array formulas today and MATCH and INDEX
functions, but don't even know if this is doable with (these)
functions.

Thanks for any replies.

 

[Bob Phillips]

=INDEX(Sheet2!C1:C100,MATCH(1,(Sheet1!A1:A100="Type A")*(Sheet1!B1:B100="Ver
2"),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[clanofmiller]

Thanks so much, you've really helped me solve the issue I've had.

Just for the sake of understanding what I'm doing, what does the 1
stand for in the lookup_value field of the MATCH func.? Thanks.

 

[clanofmiller]

Thanks so much, you've really helped me solve the issue I've had.

Just for the sake of understanding what I'm doing, what does the 1
stand for in the lookup_value field of the MATCH func.? Thanks.

 

[Bob Phillips]

It is the value being matched as the (test)*(test) parts of the function
will return an array of 1 and 0, so by matching 1, you find the index of the
first match.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)