IF N/A

scoobydoo

New Member
Joined
Jan 6, 2010
Messages
4
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.

1615581640220.png
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,659
Office Version
  1. 365
Platform
  1. MacOS
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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)
 
Solution

scoobydoo

New Member
Joined
Jan 6, 2010
Messages
4
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
Joined
Jan 6, 2010
Messages
4

ADVERTISEMENT

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. :)
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,659
Office Version
  1. 365
Platform
  1. MacOS

jtakw

has answered, now i see what you are after
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,386
Messages
5,636,015
Members
416,892
Latest member
Bensch

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top