IF N/A

[scoobydoo]

Hello! I'm trying to create a formula which checks to see if the value in multiple cells = N/A, to return blanks otherwise return another value. I'm looking at the data by row. For example using the data below. The formula in G2 would be IF B2 = #N/A then Blank, otherwise = B1; IF C2 = #N/A then Blank, otherwise = C1; IF D2 = #N/A then Blank, otherwise = D1; and IF E2 = #N/A then Blank, otherwise = E1. There should only be one value for each row, but need a quick way to evaluate, since the value is not in the same column each time.

 

[etaf]

if( iserror(B2),"",B1)
OR
IF(ISNA(B2),"",B1)

You have in row 2 - B2 & C2 & D2 =#N/A and E2 = 32
what should G2 have ?

Perhaps give some expected results in G

 

[jtakw]

Hi,

You can do this with LOOKUP:

Book3.xlsx
	A	B	C	D	E	F	G
1		25	23	22	21
2		#N/A	#N/A	#N/A	32		21
3		#N/A	21	#N/A	#N/A		23
Sheet837

Cell Formulas
Range	Formula
G2:G3	G2	=LOOKUP(9.99999999999999E+307,B2:E2,B$1:E$1)

 

[scoobydoo]

if( iserror(B2),"",B1)
OR
IF(ISNA(B2),"",B1)

You have in row 2 - B2 & C2 & D2 =#N/A and E2 = 32
what should G2 have ?

Perhaps give some expected results in G

The results for row 2 should be "021 Coverage", since E2 is the only column with a value of 32.

 

[scoobydoo]

Hi,

You can do this with LOOKUP:

Book3.xlsx
	A	B	C	D	E	F	G
1		25	23	22	21
2		#N/A	#N/A	#N/A	32		21
3		#N/A	21	#N/A	#N/A		23
Sheet837

Cell Formulas
Range	Formula
G2:G3	G2	=LOOKUP(9.99999999999999E+307,B2:E2,B$1:E$1)

Hello jtakw,

That worked perfectly! It made it seem so easily done. I've used VLOOKUP before, but never LOOKUP. Thank you so much for your help! I've learned something new today.

 

[etaf]

jtakw​

has answered, now i see what you are after

 

[jtakw]

You're welcome, thanks for the feedback.