| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP MATCH INDEX two conditions / criterias text and date | Excel Worksheet Functions | |||
| index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
| lookup with INDEX MATCH formule depending on 2 conditions | Excel Worksheet Functions | |||
| Match/Index return #N/A | Excel Worksheet Functions | |||
| Error Return Value from and INDEX(A:2,MATCH()) function | Excel Worksheet Functions | |||
Linear Mode
