lookup formula with isna returning #value

Ivat

New Member
Joined
Mar 28, 2013
Messages
2
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
datenarrationAmountaccountIncome from service drawingssinking find
1/02/2013sales 1000income from services1000#VALUE!#VALUE!
1/02/2013drawing-600drawings#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
slowing going mad. :confused:

any and all help greatly appreciated.

Thanking you in advance.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
datenarrationAmountaccountIncome from service drawingssinking find
1/02/2013sales1000income from services1000#VALUE!#VALUE!
1/02/2013drawing-600drawings#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
slowing going mad. :confused:

any and all help greatly appreciated.

Thanking you in advance.

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
datenarrationAmountaccountIncome from service drawingssinking find
1/02/2013sales1000income from services1000#VALUE!#VALUE!
1/02/2013drawing-600drawings#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
slowing going mad. :confused:

any and all help greatly appreciated.

Thanking you in advance.
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?)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top