Nested IfError

[chaitanyapatel88]

Hi,

I have a requirement where I need to check if value of a cell ex: E90 is available in another sheet, if available, fetch that value, and if not available, check for value of F90 in the other sheet. I am using the below formula but unable to evaluate result. If none of the values are available, update "ABC" Can you please help?

=IFERROR(IF(LEN(TRIM($E90)))<>0,INDEX('PFIC Report'!G:G,MATCH($E90,'PFIC Report'!C:C,0)),
IFERROR(IF(LEN(TRIM($F90)))<>0,INDEX('PFIC Report'!G:G,MATCH($F90,'PFIC Report'!D:D,0)),
IFERROR(IF(LEN(TRIM($H90)))<>0,INDEX('PFIC Report'!G:G,MATCH($H90,'PFIC Report'!E:E,0)),
IFERROR(IF(LEN(TRIM($J90)))<>0,INDEX('PFIC Report'!G:G,MATCH($J90,'PFIC Report'!F:F,0)),"ABC"))))

This requirement seems to be fine with IF condition, but it gives #N/A as result.

 

[chaitanyapatel88]

I did. But it isnt returning any results. Tried with multiple combinations...

 

[chaitanyapatel88]

yep.. got it. Below works fine. Removed the unecessary LEN from the formula. Thank you so much etaf for your time. Appreciate it

=IFERROR(INDEX('PFIC Report'!G:G,MATCH(TRIM($E90),'PFIC Report'!C:C,0)),
IFERROR(INDEX('PFIC Report'!G:G,MATCH(TRIM($F90),'PFIC Report'!D:D,0)),
IFERROR(NDEX('PFIC Report'!G:G,MATCH(TRIM($H90),'PFIC Report'!E:E,0)),
IFERROR(INDEX('PFIC Report'!G:G,MATCH(TRIM($J90),'PFIC Report'!F:F,0)),"ABC"))))

 

[etaf]

you are welcome