+ Reply to Thread
Results 1 to 7 of 7

HELP: Vlookup using multiple criteria to check values

  1. 01-15-2014, 02:58 AM #1
    jasonj427
    jasonj427 is offline
    Registered User
    Join Date
    06-26-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    28

    HELP: Vlookup using multiple criteria to check values

    Hi Guys,

    Have spent ages on this to no avail. Need the attached sheet to verfiy the values in the "Reconciliation" are correct when referencing the "Prices" sheet. I need column F (Reconciliation sheet) to lookup the "Code" in column B in the "Prices" sheet and then further look up the "Payment Frequency" (column D in the Reconciliation sheet) in the "Prices" sheet to verify that the price is correct.

    Due to slight decimal point issues, if the value has a greater than 0.02 difference then display "ERROR" otherwise 0.

    Thanks a thousand times over,
    Jason.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-15-2014, 03:05 AM #2
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: HELP: Vlookup using multiple criteria to check values

    hi Jason. can i say that the column A & B in Prices sheet combined are a unique combination & wont appear more than twice there? if so, this in F2 would give you the price:
    =SUMIFS(Prices!D:D,Prices!A:A,A2,Prices!B:B,B2)

    if you want to compare with column E, then:
    =IF(ABS(E2-SUMIFS(Prices!D:D,Prices!A:A,A2,Prices!B:B,B2))>0.02,"ERROR",0)

    Edit: i think i got your question wrong. FDibbins' answer should do it
    Last edited by benishiryo; 01-15-2014 at 03:14 AM.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
  3. 01-15-2014, 03:10 AM #3
    FDibbins
    FDibbins is offline
    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,946

    Re: HELP: Vlookup using multiple criteria to check values

    Try this, copied down...
    =IF(ABS(INDEX(Prices!$A$1:$H$17,MATCH(Reconciliation!B2,Prices!$B$1:$B$17,0),MATCH(Reconciliation!D2,Prices!$A$1:$H$1,0))-E2)>0.02,"ERROR",0)

    @ Ben, the columns in Prices that need to be used, change based on the info in Recon column D
    Last edited by FDibbins; 01-15-2014 at 03:14 AM.
    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
    Reply With QuoteRegister To Reply
  4. 01-15-2014, 06:07 PM #4
    jasonj427
    jasonj427 is offline
    Registered User
    Join Date
    06-26-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: HELP: Vlookup using multiple criteria to check values

    What if I wanted to display the value of difference besides the word error? would I just replace the error with MATCH(Reconciliation!B2,Prices!$B$1:$B$17,0),MATCH(Reconciliation!D2,Prices!$A$1:$H$1,0))-E2)?
    Reply With QuoteRegister To Reply
  5. 01-15-2014, 06:55 PM #5
    FDibbins
    FDibbins is offline
    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,946

    Re: HELP: Vlookup using multiple criteria to check values

    You would need to change it to this...
    =IF(ABS(INDEX(Prices!$A$1:$H$17,MATCH(Reconciliation!B2,Prices!$B$1:$B$17,0),MATCH(Reconciliation!D2,Prices!$A$1:$H$1,0))-E2)>0.02,"ERROR "&TEXT(ABS(INDEX(Prices!$A$1:$H$17,MATCH(Reconciliation!B2,Prices!$B$1:$B$17,0),MATCH(Reconciliation!D2,Prices!$A$1:$H$1,0))-E2),"0.0000"),0)

    Adjust the bolded part to show more or less decimal places
    Reply With QuoteRegister To Reply
  6. 01-15-2014, 08:45 PM #6
    jasonj427
    jasonj427 is offline
    Registered User
    Join Date
    06-26-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: HELP: Vlookup using multiple criteria to check values

    Thank you so much, brilliant brilliant brilliant!!!
    Reply With QuoteRegister To Reply
  7. 01-15-2014, 09:00 PM #7
    FDibbins
    FDibbins is offline
    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,946

    Re: HELP: Vlookup using multiple criteria to check values

    Happy to help, and thank your for the kind words and feedback
    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. possible to check for file in multiple folders, then check for values in specific cells?
    By mattheritage in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-04-2014, 08:33 AM
  2. Check if a cell contains multiple values and return multiple specific values
    By hollieb in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-04-2013, 09:49 AM
  3. Select multiple criteria based on check box selection and return all unique values.
    By TommyToe in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-21-2013, 09:14 AM
  4. Multiple Criteria - Vlookup for numerical values
    By n_nagesh in forum Excel General
    Replies: 10
    Last Post: 08-28-2009, 08:09 AM
  5. using vlookup tp check for criteria
    By eric_t_viking in forum Excel General
    Replies: 3
    Last Post: 05-29-2009, 03:53 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