+ Reply to Thread
Results 1 to 10 of 10

Using a different IF inside IFS statement

  1. #1
    Registered User
    Join Date
    06-18-2022
    Location
    Ohio
    MS-Off Ver
    Office 365 Online
    Posts
    24

    Using a different IF inside IFS statement

    Here is my current formula in cell 'Billing Master'!G2:

    =IF(C2="","",IFS('Current Billing'!C2="PT",VLOOKUP(C2&D2,'Patient List'!$A$2:$AM$69,5,0),'Current Billing'!C2="OT",VLOOKUP(C2&D2,'Patient List'!$A$2:$AM$69,6,0),'Current Billing'!C2="ST",VLOOKUP(C2&D2,'Patient List'!$A$2:$AM$69,7,0),'Current Billing'!C2="",#N/A))

    This accomplishes the following that I need, in order:
    1. Returns blank if C2 is blank
    2. Returns #N/A if 'Current Billing!C2 is blank
    3. Returns date from VLOOKUP based on 'Current Billing!C2= PT or OT or ST (different cells referenced based on cell value)

    What I need it to do is one more check: If the result of the indicated VLOOKUP is blank, return #N/A. So, if PT Eval Date (cell 'Patient List'!E5) is blank, but it does return "PT" in cell 'Current Billing'!C2, it returns #N/A. Same for OT and ST with blank 'Patient List'!E6 and E7 respectively. I tried to put an IF statement inside of the IFS statement, but I think that just confused it.

    Any ideas? I'm also open to a better idea than the IFS statement that would allow me to embed the last IF statements I need. Thanks!
    Attached Files Attached Files
    Last edited by Rolaids24; 06-09-2024 at 03:58 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,696

    Re: Using a different IF inside IFS statement

    Try:
    Please Login or Register  to view this content.
    Last edited by protonLeah; 06-09-2024 at 06:22 PM.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    06-18-2022
    Location
    Ohio
    MS-Off Ver
    Office 365 Online
    Posts
    24

    Re: Using a different IF inside IFS statement

    So close! That last #N/A doesn't seem to be working. When cell 'Patient List!E10 is blank, it returns 1/0/1900 instead of #N/A.

    Thank you for trying to help me!
    Last edited by Rolaids24; 06-09-2024 at 06:51 PM.

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2021
    Posts
    6,046

    Re: Using a different IF inside IFS statement

    Try this in G2:
    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,932

    Re: Using a different IF inside IFS statement

    I would add that since your "Patient List" is in an EXCEL Table, you can use that for reference. Another option is this:

    =LET(a,INDEX(Table1,MATCH(C2&D2,Table1[Lookup],0),MATCH('Current Billing'!C2&" Eval Date",Table1[#Headers],0)),IF(C2="","",IF(a=0,#N/A,a)))


    Also, if you wanted to get rid of the "Lookup" column in the "Patient List", you could use this:
    =LET(a,INDEX(Table1,MATCH(C2&D2,Table1[Patient Last]&Table1[Patient First],0),MATCH('Current Billing'!C2&" Eval Date",Table1[#Headers],0)),IF(C2="","",IF(a=0,#N/A,a)))

  6. #6
    Registered User
    Join Date
    06-18-2022
    Location
    Ohio
    MS-Off Ver
    Office 365 Online
    Posts
    24

    Re: Using a different IF inside IFS statement

    That did it. Thanks, Phuocam!

  7. #7
    Registered User
    Join Date
    06-18-2022
    Location
    Ohio
    MS-Off Ver
    Office 365 Online
    Posts
    24

    Re: Using a different IF inside IFS statement

    Thanks for the alternate options, Gregb11! I'm positive those will come in handy on the next issue I face making this sheet work.

  8. #8
    Registered User
    Join Date
    06-18-2022
    Location
    Ohio
    MS-Off Ver
    Office 365 Online
    Posts
    24

    Re: Using a different IF inside IFS statement

    =IF(C2="","",IFERROR(1/(1/VLOOKUP(C2&D2,'Patient List'!$A$2:$AM$69,MATCH('Current Billing'!C2&" Eval Date",'Patient List'!$A$1:$U$1,0),0)),#N/A))
    Well, there's one more unforseen issue with this code. 'Current Billing'!C2 can also have PTA and OTA in the cell, which corresponds with PT and OT respectively, but that cell has to stay as PTA and OTA. Is there a way to make this formula read that cell as PT when it says PTA and OT when it says OTA?
    Last edited by Rolaids24; 06-11-2024 at 11:08 AM.

  9. #9
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2021
    Posts
    6,046

    Re: Using a different IF inside IFS statement

    Try change to:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    06-18-2022
    Location
    Ohio
    MS-Off Ver
    Office 365 Online
    Posts
    24

    Re: Using a different IF inside IFS statement

    That didn't work, but when I moved the LEFT just to the left of the MATCH it worked perfectly! Which I believe was your original intent. You're a genius, Phuocam. Thanks again!

    Please Login or Register  to view this content.

+ 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. If Statement inside For Next
    By nkitchen31 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-16-2021, 08:47 AM
  2. [SOLVED] IF Statement with Multi-Criteria Statement inside a Data Validation List
    By dharvey1978 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-31-2020, 09:10 PM
  3. Need help with a S Q L statement inside VBA
    By VAer in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-30-2018, 08:32 AM
  4. [SOLVED] Can You Put An IF Statement Inside a REPLACE Statement?
    By nevi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-21-2014, 07:34 AM
  5. Using a VLOOKUP and IF statement inside an IF statement
    By Isis3 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-08-2011, 07:04 PM
  6. Using Next inside an IF statement
    By ChemistB in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-13-2010, 10:27 AM
  7. If, then inside a for, next statement?
    By EnergyEngineer in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-10-2007, 05:04 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