# IF? - Lookup a number

#### mypig7

##### Active Member
I have 4 cells. A2,A3,A4,A5
3 of the cells contain the word "Fault" and 1 contains a number e.g. 55 (this number can vary though)
I want to enter a forumla (say in A1) so that if one of the cells in the range A2:A5 contains a number that number appears in A1.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Is it only one cell that will contain a number?

If yes, try: =LOOKUP(9.9999999999E+307,A2:A5)

This works, in cell B2 Paste the following formula
=SMALL(\$A\$2:\$A\$5,0+1)
Then in cell A1 enter the formula A1 = C3

thanks v much

Thanks.

If I get #NUM! appearing because no number appears is there any way I can get the letters FAULT to appear instead of #NUm!

Thanks

=If(isnumber(formula),formula,"FAULT)

replace formula with the actual formula you used

Perhaps:

=IF(COUNT(A2:A5)=1,SUM(A2:A5),"FAULT")

Replies
1
Views
78
Replies
2
Views
124
Replies
4
Views
185
Replies
4
Views
378
Replies
2
Views
350

1,207,089
Messages
6,076,517
Members
446,211
Latest member
b306750

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