+ Reply to Thread
Results 1 to 8 of 8

Formula to query 3 columns and return a value if appropriate.

  1. 07-24-2017, 09:58 AM #1
    mmaskiell
    mmaskiell is offline
    Registered User
    Join Date
    09-20-2011
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Formula to query 3 columns and return a value if appropriate.

    I am trying to automate a checkbook function for my home (I track finances in Excel). I simply want to be able to verify (on the "JUN" tab)- for any row, that a value exists in column D (the amount of a check), Column G (check number), and a textual character in column H (I use "Y", "y", "X", "x" as valid criteria to mark the 'check' cleared).

    After validating if a check is cleared or not, I wish to take check values which have NOT cleared and sum them up on the second tab. On the tab of my workbook "June subtotals". I have a lengthy IF statement:
    =IF(AND(JUN!$D2<>"",AND(JUN!$G2<>"",OR(JUN!$H2="Y",JUN!$H2="y",JUN!$H2="X",JUN!$H2="x"))),"",JUN!$D2)

    It is currently returning information even when I have nothing in Column G ("Check #"). Any chance someone could help?

    Currently, I use conditional formatting on the "JUN" tab to turn the cell green for a cleared check, if one of the specific values is input in that column.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-24-2017, 10:05 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,728

    Re: Formula to query 3 columns and return a value if appropriate.

    Welcome to the forum!

    Firstly, there are not enough sample data in the attachment to make it completely clear what you are trying to do - what is the expected result of the formula?

    Secondly, Excel sees no difference between X and x, Y and y, (it's not case sensitive) so there is no point checking them twice.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-24-2017, 11:06 AM #3
    mmaskiell
    mmaskiell is offline
    Registered User
    Join Date
    09-20-2011
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Formula to query 3 columns and return a value if appropriate.

    First- thanks for the follow-up question.
    Second- thank you for that tidbit; it just made this easier!

    In simple context, I'm trying to validate that IF {JUN tab}, column D has a value (number), column G has a value (number) AND column H DOES NOT contain a Y or X, show column D value on 2nd worksheet {Jun subtotal tab}.
    If column D contains a value, BUT column G is blank, populate no value on the 2nd tab {June subtotals} for that row.

    Make better sense?
    Last edited by AliGW; 07-24-2017 at 11:11 AM. Reason: Unnecessary quotation removed.
    Reply With QuoteRegister To Reply
  4. 07-24-2017, 11:20 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,728

    Re: Formula to query 3 columns and return a value if appropriate.

    OK, well your formula is working, then, as far as I can see. It can be simplified to this:

    =IF(AND(JUN!$D2<>"",JUN!$G2<>"",OR(JUN!$H2="Y",JUN!$H2="X")),"",JUN!$D2)

    If the outcomes on the summary tab are not what you are expecting, then you need to tell us the results you ARE expecting.
    Reply With QuoteRegister To Reply
  5. 07-24-2017, 11:28 AM #5
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,180

    Re: Formula to query 3 columns and return a value if appropriate.

    TRy

    =IF(AND(ISNUMBER(JUN!$D2),ISNUMBER(JUN!$G2),JUN!$H2<>"Y",JUN!$H2<>"X"),JUN!$D2,"")
    Reply With QuoteRegister To Reply
  6. 07-24-2017, 11:31 AM #6
    mmaskiell
    mmaskiell is offline
    Registered User
    Join Date
    09-20-2011
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Formula to query 3 columns and return a value if appropriate.

    Perplexed by Row 5 results. I copied and pasted the shorter statement you included- and I agree it works....MOSTLY. Row 5 contains a BLANK in column G, but translates a value on the 2nd tab.

    Any ideas on that?

    Column G is a discriminator- if there is a value, it should be considered for population on 2nd tab. If Column G contains no value (is blank), then it should not be considered for population on the second tab.

    Thank you for this help!
    Last edited by mmaskiell; 07-24-2017 at 11:34 AM.
    Reply With QuoteRegister To Reply
  7. 07-24-2017, 11:40 AM #7
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,585

    Re: Formula to query 3 columns and return a value if appropriate.

    Try this

    =IF(OR(AND(JUN!$D2<>"",JUN!$G2<>""),OR(JUN!$H2="Y",JUN!$H2="y",JUN!$H2="X",JUN!$H2="x")),"",JUN!$D2)
    Reply With QuoteRegister To Reply
  8. 07-24-2017, 10:26 PM #8
    mmaskiell
    mmaskiell is offline
    Registered User
    Join Date
    09-20-2011
    Location
    Orlando, FL
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Formula to query 3 columns and return a value if appropriate.

    This works perfectly. I need to do some research on ISNUMBER- I have to believe that's the ticket!

    Thank you all!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. MS Query - query to return value based on two joins within the same tables
    By smk224 in forum Excel General
    Replies: 11
    Last Post: 12-14-2016, 04:41 PM
  2. Formula to remove data from a query result and create columns with that info seperated
    By BG1983 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-03-2015, 09:53 AM
  3. [SOLVED] Formula Query: How to calculate the difference between two columns of data?
    By The_Snook in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-25-2012, 08:14 AM
  4. Formula that return if any entry appear in all columns
    By bharatp83 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-12-2012, 05:25 AM
  5. Query Data in hidden rows/columns with Query Box
    By VincetOmnia in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2009, 10:53 PM
  6. Need formula to query an array and return a value
    By rlerner in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-29-2009, 02:39 PM
  7. MS query return multiple columns in Excel
    By normajmarsh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-13-2006, 04:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1