# lookup formula with isna returning #value

#### Ivat

##### New Member
Hi

I am preparing a spreadsheet where I want the amount entered and categorised on the left to automatically transfer to the classification area

my formula is

=IF(ISNA(Sheet1!\$C4),0,LOOKUP(2^15,SEARCH(E\$3,\$D4),\$C4))

Started with just the lookup which worked well but put all transactions on the first line.

line one if there is income from services then return \$1000. all other cells should be zero or blank

I don't how to use macros for this.

this how the spread sheet looks
 Classification date narration Amount account Income from service drawings sinking find 1/02/2013 sales 1000 income from services 1000 #VALUE! #VALUE! 1/02/2013 drawing -600 drawings #VALUE! -600 #VALUE! 1/02/2013 1/02/2013 1/02/2013

<colgroup><col><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

have tried if(not(isna, if(1-ISNA... all say too many variables in the formula

any and all help greatly appreciated.

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Robert Mika

##### MrExcel MVP
Hi

I am preparing a spreadsheet where I want the amount entered and categorised on the left to automatically transfer to the classification area

my formula is

=IF(ISNA(Sheet1!\$C4),0,LOOKUP(2^15,SEARCH(E\$3,\$D4),\$C4))

Started with just the lookup which worked well but put all transactions on the first line.

line one if there is income from services then return \$1000. all other cells should be zero or blank

I don't how to use macros for this.

this how the spread sheet looks
 Classification date narration Amount account Income from service drawings sinking find 1/02/2013 sales 1000 income from services 1000 #VALUE! #VALUE! 1/02/2013 drawing -600 drawings #VALUE! -600 #VALUE! 1/02/2013 1/02/2013 1/02/2013

<tbody>
</tbody>

have tried if(not(isna, if(1-ISNA... all say too many variables in the formula

any and all help greatly appreciated.

Hi

I am preparing a spreadsheet where I want the amount entered and categorised on the left to automatically transfer to the classification area

my formula is

=IF(ISNA(Sheet1!\$C4),0,LOOKUP(2^15,SEARCH(E\$3,\$D4),\$C4))

Started with just the lookup which worked well but put all transactions on the first line.

line one if there is income from services then return \$1000. all other cells should be zero or blank

I don't how to use macros for this.

this how the spread sheet looks
 Classification date narration Amount account Income from service drawings sinking find 1/02/2013 sales 1000 income from services 1000 #VALUE! #VALUE! 1/02/2013 drawing -600 drawings #VALUE! -600 #VALUE! 1/02/2013 1/02/2013 1/02/2013

<tbody>
</tbody>

have tried if(not(isna, if(1-ISNA... all say too many variables in the formula

any and all help greatly appreciated.

You have #VALUE error so ISNA will not work(it "takes" only #N/A error)
You can use IFERROR or ISERROR(depends on Excel version)
But..
You current layout is little bit...blur
Is your aim to look for the 1000 from Income service in Amount?
One way would be:

Excel 2010
ABCDE
1
2Classification
3datenarrationAmountaccountIncome from service
41/2/2013sales1000income from services1000
51/2/2013drawing-600drawings0
61/2/2013
71/2/2013
81/2/2013
Sheet1
<p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E4</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">SEARCH(<font color="Green">E\$3,\$D4</font>)</font>),\$C4,0</font>)</td></tr></tbody></table></td></tr></table><br />
is F4 and G4 populate with similar formula?
(you are dragging from E4?)

#### Ivat

##### New Member
Hi Robert,

Thank you for your reply, It works perfectly. I am dragging across and down from E4 as there will be more classifications. I am using Excel2007

I did try doing a screen dump of what I had in excel but could not get it into the page.

For future reference how did you do it?

Thank you again so much. Have a great weekend

Irene from Australia

Replies
6
Views
621
Replies
6
Views
315
Replies
10
Views
437
Replies
6
Views
175
Replies
7
Views
253

1,195,924
Messages
6,012,341
Members
441,691
Latest member
starlightmuse

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