Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 211
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 515
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 211
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 211
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Setting multiple conditions to return a figure from multiple cells Sapper Excel Discussion (Misc queries) 4 April 26th 09 10:33 PM
Lookup with multiple conditions Svenvlad Excel Worksheet Functions 8 February 2nd 06 06:30 PM
Lookup with multiple conditions Sachin Narute Excel Discussion (Misc queries) 2 August 2nd 05 07:02 AM
Lookup with multiple conditions linglc Excel Discussion (Misc queries) 4 July 28th 05 04:44 PM
How to multiple conditions to validate more than 2 conditions to . Bhuvana Govind Excel Worksheet Functions 1 January 28th 05 08:07 PM


All times are GMT +1. The time now is 02:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"