# IF N/A

#### scoobydoo

##### New Member
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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### etaf

##### Well-known Member
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

##### Well-known Member
Hi,

You can do this with LOOKUP:

Book3.xlsx
ABCDEFG
125232221
2#N/A#N/A#N/A3221
3#N/A21#N/A#N/A23
Sheet837
Cell Formulas
RangeFormula
G2:G3G2=LOOKUP(9.99999999999999E+307,B2:E2,B\$1:E\$1)

#### scoobydoo

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

##### New Member

Hi,

You can do this with LOOKUP:

Book3.xlsx
ABCDEFG
125232221
2#N/A#N/A#N/A3221
3#N/A21#N/A#N/A23
Sheet837
Cell Formulas
RangeFormula
G2:G3G2=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.

#### jtakw​

has answered, now i see what you are after

#### jtakw

##### Well-known Member
You're welcome, thanks for the feedback.

Replies
0
Views
59
Replies
3
Views
105
Replies
13
Views
194
Replies
2
Views
160
Replies
4
Views
122

1,129,687
Messages
5,637,833
Members
416,985
Latest member
mrindira

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back