Need to correct #N/A

[dlittle16]

Hello,

I'm having a huge issue with this formula could someone help, cell H186 and D186, needs to read Humana. This info is generated from the EFT Client Lookup tab and EFT Client Lookup tab.

[JieJenn]

In D186, update your formula to =IFERROR(IF(A186="","",VLOOKUP(TRIM(RIGHT('Bank Txns'!L185,LEN('Bank Txns'!L185)-41)),'EFT Client Lookup'!$A:$B,2,0)),"")

[dlittle16]

Hi JieJenn,

How can I update cell D186 with actual content(Humana) instead of an empty string value?

[JieJenn]

Not sure I follow your question. Your cell D186 is a formula.

[dlittle16]

D186 is blank.

[dlittle16]

Cell d186 needs to say Humana, a trim formula is pulling from the Bank Txns tab in the formula, but its not reading the Eft Client Lookup tab.

[JieJenn]

Your Vlookup returns no result. The IFError function converts Error to blank in this case. You can change the formula accordingly base on how you want to handle the error (#N/A)

=IFERROR(IF(A186="","",VLOOKUP(TRIM(RIGHT('Bank Txns'!L185,LEN('Bank Txns'!L185)-41)),'EFT Client Lookup'!$A:$B,2,0)),"")

[dlittle16]

Excellent!

Same case for cell L186-P186? Would you pleas provide an example?

[JieJenn]

Just put IFError outside your function. For L186, it will looks like

=IFERROR(IF(A186="","",IF(VLOOKUP(CONCATENATE(Receiving_Co,Txn_Pay_Type),'Coding by pymt type'!$A:$E,5,0)="","",CONCATENATE(IF(AND('Bank Txns'!F185="HA- Operating",'Bank Txns'!I185=20),3,VLOOKUP('Bank Txns'!F185,'Bank Account Lookup Table'!A:C,3,0)),"01-",VLOOKUP("HARecords",'Coding by pymt type'!$A:$E,5,0)))), "")