# Using a different IF inside IFS statement

1. ## 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!

2. ## Re: Using a different IF inside IFS statement

Try:
``Please Login or Register  to view this content.``

3. ## 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!

4. ## Re: Using a different IF inside IFS statement

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

5. ## 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. ## Re: Using a different IF inside IFS statement

That did it. Thanks, Phuocam!

7. ## 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. ## 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?

9. ## Re: Using a different IF inside IFS statement

Try change to:

``Please Login or Register  to view this content.``

10. ## 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.``

#### Thread Information

##### Users Browsing this Thread

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

#### 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