+ Reply to Thread
Results 1 to 6 of 6

If Statement with two criteria and a matching function

  1. #1
    Registered User
    Join Date
    06-26-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    20

    If Statement with two criteria and a matching function

    I need an IF formula a column F that says If the Patient code in column A matches any of the codes in column J and if the Payer in column B is Blue Cross then give me Charge 2 in Column D, otherwise give me Charges in Column C. For Example, the first two entries do not meet the criteria so column C should be populated, by the 3rd entry that has 101 and Blue Cross should be populated as Column D charge of 215
    Attached Files Attached Files

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: If Statement with two criteria and a matching function

    try in F2 and copy down

    =IF(AND(B2="Blue Cross",ISNUMBER(MATCH(A2,$J$2:$J$7,0))),D2,C2)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    06-26-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: If Statement with two criteria and a matching function

    The data source that I have has a datadump for column A, inpatient code, and is not formatted as a number. If that said, the match is not being found because the ISNUMBER is looking for a number format. One way around this is to multiply all the numbers by 1 in column A. What can I do to the formula to go around this?

  4. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: If Statement with two criteria and a matching function

    Maybe use the *1 in the formula itself..

    =IF(AND(B2="Blue Cross",ISNUMBER(MATCH(A2*1,$J$2:$J$7,0))),D2,C2)

  5. #5
    Registered User
    Join Date
    06-26-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: If Statement with two criteria and a matching function

    That worked thanks

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: If Statement with two criteria and a matching function

    If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.

    New quick method:
    Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word "Title" you will see a dropdown with the words "No prefix".
    Change to "Solved"
    Click Save

+ 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