Return value with 2 conditions (using Lookup/Match/Index)
 

I am looking for a formula to return 1 value with 2 criteria. There are 2
excel sheet:
Sheet 1 - Row A (city), Row B (airlines)
Sheet 2 - Row A (airline), Row B (city), Row C (airlines rate)

If in sheet 1, cell A3 Indonesia and flying with SIA Airlines (cell B3), how
can make use Sheet 2 data of Row C with the condition of "Indonesia" and
"SIA"(2 conditions) to pull out the airlines rate.

Any main formula, I've searched through MATCH, VLOOKUP, INDEX, most of them
only cater to 1 look up value condition. Pls help. Thanks.

Return value with 2 conditions (using Lookup/Match/Index)
 

Try this formula in cell Sheet1 C3. I have assumed in Sheet2 you have 10
rows...If there are more change the 10 to the rowcount.

=SUMPRODUCT(--(Sheet2!$A$1:$A$10=B3),--(Sheet2!$B$1:$B$10=A3),--(Sheet2!$C$1:$C$10))


If this post helps click Yes
---------------
Jacob Skaria


"Venice" wrote:

I am looking for a formula to return 1 value with 2 criteria. There are 2
excel sheet:
Sheet 1 - Row A (city), Row B (airlines)
Sheet 2 - Row A (airline), Row B (city), Row C (airlines rate)

If in sheet 1, cell A3 Indonesia and flying with SIA Airlines (cell B3), how
can make use Sheet 2 data of Row C with the condition of "Indonesia" and
"SIA"(2 conditions) to pull out the airlines rate.

Any main formula, I've searched through MATCH, VLOOKUP, INDEX, most of them
only cater to 1 look up value condition. Pls help. Thanks.

Return value with 2 conditions (using Lookup/Match/Index)
 

Thanks Jacob. It really works!

May i know what's the purpose of putting "--+ in each of the array?

"Jacob Skaria" wrote:

Try this formula in cell Sheet1 C3. I have assumed in Sheet2 you have 10
rows...If there are more change the 10 to the rowcount.

=SUMPRODUCT(--(Sheet2!$A$1:$A$10=B3),--(Sheet2!$B$1:$B$10=A3),--(Sheet2!$C$1:$C$10))


If this post helps click Yes
---------------
Jacob Skaria


"Venice" wrote:

I am looking for a formula to return 1 value with 2 criteria. There are 2
excel sheet:
Sheet 1 - Row A (city), Row B (airlines)
Sheet 2 - Row A (airline), Row B (city), Row C (airlines rate)

If in sheet 1, cell A3 Indonesia and flying with SIA Airlines (cell B3), how
can make use Sheet 2 data of Row C with the condition of "Indonesia" and
"SIA"(2 conditions) to pull out the airlines rate.

Any main formula, I've searched through MATCH, VLOOKUP, INDEX, most of them
only cater to 1 look up value condition. Pls help. Thanks.

Return value with 2 conditions (using Lookup/Match/Index)
 

Have a look at the following link...

http://www.xl-central.com/lookup-mulitple-criteria.html

Hope this helps!

In article ,
Venice wrote:

I am looking for a formula to return 1 value with 2 criteria. There are 2
excel sheet:
Sheet 1 - Row A (city), Row B (airlines)
Sheet 2 - Row A (airline), Row B (city), Row C (airlines rate)

If in sheet 1, cell A3 Indonesia and flying with SIA Airlines (cell B3), how
can make use Sheet 2 data of Row C with the condition of "Indonesia" and
"SIA"(2 conditions) to pull out the airlines rate.

Any main formula, I've searched through MATCH, VLOOKUP, INDEX, most of them
only cater to 1 look up value condition. Pls help. Thanks.