+ Reply to Thread
Results 1 to 14 of 14

Help with Vlookup or IF Statements formula.

  1. #1
    Registered User
    Join Date
    03-25-2012
    Location
    Durham, NC
    MS-Off Ver
    Excel 2013
    Posts
    12

    Help with Vlookup or IF Statements formula.

    I need help with a vlookup formula but I'm unclear if I need to use an IF statement or Vlookup

    Example of what I'm try to do:
    If I lookup File A and lookup File B and they both equal then I want to pull data from File B – U2 into File A P2, if not then I want 0

    File A
    A2 J2 P2
    201010 611100

    File B
    A2 J2 U2
    201010 611100 178640.00

    Below is the Formula I was trying to use with no luck.

    =VLOOKUP(A2,'[FY 11-12 Base Budget (Final).xlsx]Summary'!$A$1:$V$1835,21,TRUE) and VLOOKUP(J2,'[FY 11-12 Base Budget (Final).xlsx]Summary'!$J$1:$U$1835,21,TRUE)
    Last edited by Tturner; 03-28-2012 at 09:10 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Help with Vlookup or IF Statements formula.

    Actrually you need to combine both. If would be much easier if you had included 2 sample files, but try this...adjust your references as needed..

    Please Login or Register  to view this content.
    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

  3. #3
    Forum Contributor darknation144's Avatar
    Join Date
    01-24-2012
    Location
    London
    MS-Off Ver
    Microsoft Excel 365 MSO
    Posts
    555

    Re: Help with Vlookup or IF Statements formula.

    Did this solution work for you?

  4. #4
    Registered User
    Join Date
    03-25-2012
    Location
    Durham, NC
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Help with Vlookup or IF Statements formula.

    Hey FDibbins,

    I got pulled on another project, I'm just getting back to this. I'm attaching my test files.

    If Column A (FundID) and Column J (Account) in both files equal I want the amount in Test 2, column U (approvedAmt) show in Test 1, column P (FY11-12 Base Budget) if not show 0.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-25-2012
    Location
    Durham, NC
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Help with Vlookup or IF Statements formula.

    Not sure, I just sent updated information,

    If Column A (FundID) and Column J (Account) in both files equal I want the amount in Test 2, column U (approvedAmt) show in Test 1, column P (FY11-12 Base Budget) if not show 0.
    Attached Files Attached Files

  6. #6
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Help with Vlookup or IF Statements formula.

    hello
    maybe like this??

    Please Login or Register  to view this content.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  7. #7
    Registered User
    Join Date
    03-25-2012
    Location
    Durham, NC
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Help with Vlookup or IF Statements formula.

    Hey vlady

    Thanks for the suggestion; I took out the absolute values, because I copied the formula down. However it only worked for the first two rows because they matched. Because Test 2 has extra rows than Test 1, the vlookup was the only way I can think of search. Any other suggestions are welcomed.

  8. #8
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Help with Vlookup or IF Statements formula.

    hello
    i didn't check if the output is correct.

    =INDEX('[Test 2.xlsx]Sheet1'!$U$2:$U$47,MATCH(A2&J2,'[Test 2.xlsx]Sheet1'!$A$2:$A$47&'[Test 2.xlsx]Sheet1'!$J$2:$J$47,0))

  9. #9
    Registered User
    Join Date
    03-25-2012
    Location
    Durham, NC
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Help with Vlookup or IF Statements formula.

    Vlady,

    The output is coming out to N/A, I can't figure out the formula, see output below

    Test 1
    A2 J2 P2
    201010 611100

    Test 2
    A2 J2 U2
    201010 611100 = output 178640.00 for Test 1 P2
    output 143197.00 for Test 1 P3
    output 0 for Test 1 P4

  10. #10
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Help with Vlookup or IF Statements formula.

    sorry array formula -- ctrl + shift + enter

    edited for "na" values

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    03-25-2012
    Location
    Durham, NC
    MS-Off Ver
    Excel 2013
    Posts
    12

    Re: Help with Vlookup or IF Statements formula.

    Vlady,

    I'm attaching the files, I'm not getting any outputs.
    Attached Files Attached Files

  12. #12
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Help with Vlookup or IF Statements formula.

    here the file for test 1

    Test 1.xlsx

  13. #13
    Registered User
    Join Date
    03-25-2012
    Location
    Durham, NC
    MS-Off Ver
    Excel 2013
    Posts
    12

    Solved: Help with Vlookup or IF Statements formula.

    This works great

  14. #14
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Help with Vlookup or IF Statements formula.

    your welcome. thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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