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.

 

[etaf]

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

does that retune the correct result
If you are getting an N/A, it maybe one of the 90 cells is OK , but there is nothing in the index/match

as you can see here
We look at the value in C
and then look that up in column B and return C

BUT 4 does not exist and so we get N/A

Book1
	A	B	C	D
2	1	1	4	#N/A
3	2	1
4	3	1
Sheet1

Cell Formulas
Range	Formula
D2	D2	=INDEX(A2:A9,MATCH(C2,B2:B9,0))

 

[etaf]

otherwise I see what you may be getting at

IF(LEN(TRIM($E90))<>0,IFERROR(INDEX('PFIC Report'!G:G,MATCH($E90,'PFIC Report'!C:C,0),"ABC),

IF(LEN(TRIM($F90))<>0,IFERROR(INDEX('PFIC Report'!G:G,MATCH($F90,'PFIC Report'!D:D,0),"ABC"),

IF(LEN(TRIM($H90))<>0,IFERROR)INDEX('PFIC Report'!G:G,MATCH($H90,'PFIC Report'!E:E,0),"ABC"),

IF(LEN(TRIM($J90))<>0,IFERROR(INDEX('PFIC Report'!G:G,MATCH($J90,'PFIC Report'!F:F,0),"ABC"),"notFound"))))

Book1
	A	B	C	D
2	1	1	4	123
3	2	1
4	3	1
Sheet1

Cell Formulas
Range	Formula
D2	D2	=IF(TRIM(C2)<>0,IFERROR(INDEX(A2:A9,MATCH(C2,B2:B9,0)),"123"),"@@@@")

 

[etaf]

Book1
	A	B	C	D
1
2	1st line	a		@@@@
3	2nd line	b	3	123
4	3rd line	c	c	3rd line
5			a	1st line
6				@@@@
7
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=IF(LEN(TRIM(C2))<>0,IFERROR(INDEX($A$2:$A$9,MATCH(C2,$B$2:$B$9,0)),"123"),"@@@@")

 

[chaitanyapatel88]

Thank you etaf for getting back. The problem I am trying to solve here is I have values in 3 columns(the 90 cell) in Sheet 1, for ex:
A1: Abc, B1: Xyz, C1: Etf

I have another look up sheet(Sheet 2) where range of values in column A,B,C of Sheet 1 are stored in different columns.

Now the requirement is, I need to check if I get a match for A1 in Sheet 2, if I don't get then I need to look for a match of B1 in Sheet 2, if I don't get then I need to look for a match of C1 in Sheet 2. If I do not get any matches then I insert say "ABC".

Thats why, in the formula I posted, I am looking if E90 is not empty, if it is not, then I am fetching its value from Sheet 2(INDEX('PFIC Report'!G:G,MATCH($E90,'PFIC Report'!C:C,0)).
If I don't get a match for E90 in Sheet 2, then I pick up F90, and if its not empty, then I fetch its value from Sheet 2(INDEX('PFIC Report'!G:G,MATCH($F90,'PFIC Report'!D:D,0))
If I don't get a match for F90 in Sheet 2, then I pick up H90, and if its not empty, then I fetch its value from Sheet 2(INDEX('PFIC Report'!G:G,MATCH($H90,'PFIC Report'!E:E,0))
If I don't get a match for H90 in Sheet 2, then I pick up J90, and if its not empty, then I fetch its value from Sheet 2(INDEX('PFIC Report'!G:G,MATCH($J90,'PFIC Report'!F:F,0))
If I don't get a match for J90 as well, I insert ABC.


=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"))))

 

[etaf]

I'm thinking the Test for LEN(TRIM($E90)))<>0 maybe redundant
if its blank then the index will return an error anyway

Just to show here , and not using a different sheet see this mock up

BUT what is the LEN(TRIM) actually to do ?

Book4
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	2nd line A	g		2nd line B	a		2nd line C	n				ABC
3	3rd line A	h		3rd line B	b		3rd line C	o			3	ABC
4	4th line A	j		4th line B	c		4th line C	p			c	4th line B
5											a	2nd line B
6											o	3rd line C
7											c	4th line B
8											n	2nd line C
9											a	2nd line B
10											h	3rd line A
11											g	2nd line A
12												ABC
13
Sheet1

Cell Formulas
Range	Formula
L2:L12	L2	=IFERROR(INDEX($A$2:$A$9,MATCH(K2,$B$2:$B$9,0)),IFERROR(INDEX($D$2:$D$9,MATCH(K2,$E$2:$E$9,0)),IFERROR(INDEX($G$2:$G$9,MATCH(K2,$H$2:$H$9,0)),"ABC")))

 

[chaitanyapatel88]

The reason I am using LEN(TRIM) is I need to trim the cell data for any unnecessary spaces/tabs and then search it on Sheet 2.

 

[etaf]

trim the cell data for any unnecessary spaces/tabs and then search it on Sheet 2.

but you are not doing that search with a trim , your JUST testing
All you are doing is trimming the cell, then testing the length to see if it is NOT zero

(IF(LEN(TRIM($E90)))<>0

However in the search itself

MATCH($E90

you are just using the cell

IN fact all the trim is doing is testing any cells that might just have a space in, removing and testign the length is not zero

 

[chaitanyapatel88]

I see your point now.. Would the below help?

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

Its just printing "ABC". Any issue in this?

 

[etaf]

try each lookup seperately and see if any return a value - if they all return N/A then the formula is correct
change one of the cells in row 90 to a value you know exists in a lookup and see if that returns OK