Show value with 2 condition in a column

[Guest]

Hi Expert,

Example , I have this table
Items Country Pric
Banana USA $ 22
Banana Canada $ 2
Mango Canada $ 25
Orange France $ 2
Apple USA $ 33
Orange Canada $ 2

What is the formula to find a price by giving 2 condition of Items and country

Thanks.

 

[Frank Kabel]

Hi
try something like the following array formula (entered with
CTRL+SHIFT+ENTER):
=INDEX(C1:C100,MATCH(1,(A1:A100="Banana")*(B1:B100="USA"),0))

or try
=SUMPRODUCT(--(A1:A100="Banana"),--(B1:B100="USA"),C1:C100)

or course you can replace the hardcoded lookup values 'Banana' and
'USA' with a cell reference

 

[Guest]

Hi Frank,

Thanks for the reply. Btw, it seems doesnt work. It show you #N/A
Any suggestion

Eager

 

[Frank Kabel]

Hi
have you entered the formula with CTRL+SHIFT+ENTER (for the first
formula). Also: does a match exist?

 

[Guest]

Yes, the second formula worked very well
For the first formula I still get a NA error, using a same table which I used second formula

Thanks.

 

[Frank Kabel]

Hi
can you post both formulas which you have used

 

[Guest]

Never mind then, because your second formula is already up to requirement
Thanks a lot
Eage