+ Reply to Thread
Results 1 to 12 of 12

Nested If searching value within Range

  1. #1
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    639

    Nested If searching value within Range

    The below code isn't quite working, any ideas how this should be worded?

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Nested If searching value within Range

    It is probably not liking the statement of C2=Centrac!B2:B1151, one cell = a range, what are you trying to achieve with this formula? Can you post a desensitized sample sheet?

    C2=Centrac!B2:B1151 is only going to match the first cell in the range, so that statement is saying the same thing as
    C2=Centrac!B2
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  3. #3
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    639

    Re: Nested If searching value within Range

    I'm trying to find the cell value within a range.

    C2 will be an account number, F2 will be a payment amount.

    I am trying to find the payment that matches the account number and the payment amount and then vlookup the payment amount if nboth values are found.
    Last edited by Sc0tt1e; 07-28-2014 at 04:03 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Nested If searching value within Range

    Well, I gathered that much from the title and the use of a vlookup. Can you upload an example of what you are trying to do? The IF(AND(C2=Centrac!B2:B1151... is what I am wondering about, as it sits that is not a valid formula, in order to help with the issue I would need to know what that part is there for.

    Is it to verify that the value you are looking for is listed on the other page, as to not return an error? If that's the case you can use an IFERROR(

    =IFERROR(VLOOKUP('Invoice Data'!F2,Centrac!A2:F1151,6,FALSE),"")

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,389

    Re: Nested If searching value within Range

    That looks like an ARRAY formula, try entering using CTRL SHIFT enter, not just enter
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    639

    Re: Nested If searching value within Range

    Quote Originally Posted by Speshul View Post
    Well, I gathered that much from the title and the use of a vlookup. Can you upload an example of what you are trying to do? The IF(AND(C2=Centrac!B2:B1151... is what I am wondering about, as it sits that is not a valid formula, in order to help with the issue I would need to know what that part is there for.

    Is it to verify that the value you are looking for is listed on the other page, as to not return an error? If that's the case you can use an IFERROR(

    =IFERROR(VLOOKUP('Invoice Data'!F2,Centrac!A2:F1151,6,FALSE),"")

    This is not error checking, I am attempting to validate data between two different systems.

    On sheet 'Invoice Data' I have the data from the bank postings, on 'Centrac' I have customer account postings. I am attempting to check the account number and payment amount on sheet one against the range of data on sheet 2. If both conditions on sheet 2 are found I want the payment amount (row 6 of the vlookup) to be returned.

    If the following conditions are met
    Please Login or Register  to view this content.
    then do the following
    Please Login or Register  to view this content.
    else leave the cell blank.
    Last edited by Sc0tt1e; 07-28-2014 at 04:21 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Nested If searching value within Range

    Ok. This might do the trick then.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Stating that a cell = a range is an array method as FDibbins says, I am not sure if that is what you are trying to do, but this formula is non-array.

    If I say A1=F5:F1000, it will always return a false unless F5=A1, even if F6 through F1000 are all identical to A1, it will be a false if F5 is not. Unless you array enter. Then it will return an array of {FALSE,TRUE,TRUE,TRUE....} (which is still false if you are looking in only one cell! :P)

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,389

    Re: Nested If searching value within Range

    If will probably help if you upload a sample of what you have, and show what you want, then we wont have to guess

  9. #9
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    639

    Re: Nested If searching value within Range

    OK file attached.

    I am searching for individual entries from Invoice data tab within the range of data on Centrac tab.

    Invoice data column C holds account numbers, column P carries out a Vlookup to see if the account number shows up on the Centrac tab, column B.

    In column Q of Invoice data I want a formula that searches Centrac for both the account number and payment amount (Column F) in Centrac, if it finds both on one line then I want the payment value of column F of Centrac for that entry.

    Hence the and if and the vlookup for the result.

    That formula did not work
    Attached Files Attached Files

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,957

    Re: Nested If searching value within Range

    Try this in Q2 of the Invoice sheet:

    =SUMIFS(Centrac!F:F,Centrac!F:F,F2,Centrac!B:B,C2)

    then copy down.

    Hope this helps.

    Pete

  11. #11
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    639

    Re: Nested If searching value within Range

    Quote Originally Posted by Pete_UK View Post
    Try this in Q2 of the Invoice sheet:

    =SUMIFS(Centrac!F:F,Centrac!F:F,F2,Centrac!B:B,C2)

    then copy down.

    Hope this helps.

    Pete
    That was the original code we started with but it adds up all the results for the account number (i.e. if there are 4 payments of 5 then 20 shows in all 4 cells in column Q).

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,957

    Re: Nested If searching value within Range

    Quote Originally Posted by Sc0tt1e View Post
    That was the original code we started with but it adds up all the results for the account number (i.e. if there are 4 payments of 5 then 20 shows in all 4 cells in column Q).
    I thought that was what you wanted. If you use VLOOKUP then it will only return the first value of any particular matching combination. Maybe you should use AVERAGEIFS instead of SUMIFS.

    Hope this helps.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 03-04-2011, 02:25 PM
  2. Excel 2007 : Searching a range
    By gannon_w in forum Excel General
    Replies: 2
    Last Post: 10-27-2010, 03:15 PM
  3. Searching a range
    By Paul987 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-30-2007, 04:36 PM
  4. searching within a range
    By jcbarnes in forum Excel General
    Replies: 2
    Last Post: 05-30-2007, 10:24 AM
  5. 3d range searching
    By thephoenix12 in forum Excel General
    Replies: 0
    Last Post: 06-15-2005, 01:18 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1