Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP on multiple conditions
Can one of you kindly devise me a formula/UDF for me so as to apply the
V/HLOOKUP on multiple conditions without creating a helper column? For instance returning the column D's entry where Column A's entry has "A" and Column B's entry is "X" and Value in Column C is greater than 10,000? Thanx in advance. -- Best Regards, Faraz |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP on multiple conditions
=IF(AND(A1="A",B1="X"C110000),D1,"")
-- HTH Kassie Replace xxx with hotmail "Faraz A. Qureshi" wrote: Can one of you kindly devise me a formula/UDF for me so as to apply the V/HLOOKUP on multiple conditions without creating a helper column? For instance returning the column D's entry where Column A's entry has "A" and Column B's entry is "X" and Value in Column C is greater than 10,000? Thanx in advance. -- Best Regards, Faraz |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP on multiple conditions
If you dont have duplicates the below will return the entry in Col C greater
than 10,000. If there are more entries above 10,000 the below will return the sum =SUMPRODUCT(--(A1:A100="A"),--(B1:B100="X"),--(C1:C10010000),C1:C100) If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: Can one of you kindly devise me a formula/UDF for me so as to apply the V/HLOOKUP on multiple conditions without creating a helper column? For instance returning the column D's entry where Column A's entry has "A" and Column B's entry is "X" and Value in Column C is greater than 10,000? Thanx in advance. -- Best Regards, Faraz |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP on multiple conditions
Thanx Jacob,
But sum is not required. It Column D may have numerical ast well as text entries. -- Do check "Yes" if this post is helpful, Best Regards, Faraz "Jacob Skaria" wrote: If you dont have duplicates the below will return the entry in Col C greater than 10,000. If there are more entries above 10,000 the below will return the sum =SUMPRODUCT(--(A1:A100="A"),--(B1:B100="X"),--(C1:C10010000),C1:C100) If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: Can one of you kindly devise me a formula/UDF for me so as to apply the V/HLOOKUP on multiple conditions without creating a helper column? For instance returning the column D's entry where Column A's entry has "A" and Column B's entry is "X" and Value in Column C is greater than 10,000? Thanx in advance. -- Best Regards, Faraz |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP on multiple conditions
Thanx Kassie,
But It is not a single cell that I require. How to apply the same on columns in complete? -- Do check "Yes" if this post is helpful, Best Regards, Faraz "Kassie" wrote: =IF(AND(A1="A",B1="X"C110000),D1,"") -- HTH Kassie Replace xxx with hotmail "Faraz A. Qureshi" wrote: Can one of you kindly devise me a formula/UDF for me so as to apply the V/HLOOKUP on multiple conditions without creating a helper column? For instance returning the column D's entry where Column A's entry has "A" and Column B's entry is "X" and Value in Column C is greater than 10,000? Thanx in advance. -- Best Regards, Faraz |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP on multiple conditions
The below will return the 1st entry
=INDEX($C$2:$C$100,MATCH(1,INDEX(($A$2:$A$100="A") *($B$2:$B$100="X")*($C$2:$C$10010000),),),) If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: Thanx Jacob, But sum is not required. It Column D may have numerical ast well as text entries. -- Do check "Yes" if this post is helpful, Best Regards, Faraz "Jacob Skaria" wrote: If you dont have duplicates the below will return the entry in Col C greater than 10,000. If there are more entries above 10,000 the below will return the sum =SUMPRODUCT(--(A1:A100="A"),--(B1:B100="X"),--(C1:C10010000),C1:C100) If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: Can one of you kindly devise me a formula/UDF for me so as to apply the V/HLOOKUP on multiple conditions without creating a helper column? For instance returning the column D's entry where Column A's entry has "A" and Column B's entry is "X" and Value in Column C is greater than 10,000? Thanx in advance. -- Best Regards, Faraz |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOKUP on multiple conditions
If you have text entries modify that to...
=INDEX($C$2:$C$100,MATCH(1,INDEX(($A$2:$A$100="A") *($B$2:$B$100="X")*(INT($C$2:$C$100)10000),),),) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: The below will return the 1st entry =INDEX($C$2:$C$100,MATCH(1,INDEX(($A$2:$A$100="A") *($B$2:$B$100="X")*($C$2:$C$10010000),),),) If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: Thanx Jacob, But sum is not required. It Column D may have numerical ast well as text entries. -- Do check "Yes" if this post is helpful, Best Regards, Faraz "Jacob Skaria" wrote: If you dont have duplicates the below will return the entry in Col C greater than 10,000. If there are more entries above 10,000 the below will return the sum =SUMPRODUCT(--(A1:A100="A"),--(B1:B100="X"),--(C1:C10010000),C1:C100) If this post helps click Yes --------------- Jacob Skaria "Faraz A. Qureshi" wrote: Can one of you kindly devise me a formula/UDF for me so as to apply the V/HLOOKUP on multiple conditions without creating a helper column? For instance returning the column D's entry where Column A's entry has "A" and Column B's entry is "X" and Value in Column C is greater than 10,000? Thanx in advance. -- Best Regards, Faraz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting multiple conditions to return a figure from multiple cells | Excel Discussion (Misc queries) | |||
Lookup with multiple conditions | Excel Worksheet Functions | |||
Lookup with multiple conditions | Excel Discussion (Misc queries) | |||
Lookup with multiple conditions | Excel Discussion (Misc queries) | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions |