nested

[HvdH]

hi. I am new to the website. I have a column with the words "cashbook", "invoice" or "credit", and next to it the amounts column. I want to us a nested function with the search function, to give the value from the amounts column, if for example the word = "invoice"
the formula currently looks like this
=IF(SEARCH("Cashbook",E4),0,AND(IF(SEARCH("Invoice",E4),J4,"")))

If the word is "cashbook", the result is 0, but in the next line ,the word is "invoice", then the result = #VALUE

please assist

 

[myall_blues]

You have a couple of problems.
Firstly, =SEARCH does not return zero if the search string is not found. Instead it returns an error, so your formula needs to trap the error.
Secondly, you can't use the AND formula like that. In your case you need a nested IF statement i.e. use another IF when the first IF evaluates to FALSE.

I hope I've interpreted what you're trying to do correctly, so try this formula. If not, come back.

=IF(NOT(ISERROR(SEARCH("Cashbook",A4))),0,IF(NOT(ISERROR(SEARCH("Invoice",A4))),E4,"Not found"))

 

[FormR]

I am new to the website.

Hi, welcome!

give the value from the amounts column, if for example the word = "invoice"

Like this maybe :

=IF(ISNUMBER(SEARCH("Invoice",A4)),E4,0)

 

[etaf]

you need isnumber()
=IF(ISNUMBER(SEARCH("cashbook",A4)),0,IF(ISNUMBER(SEARCH("invoice",A4)),E4,""))

Book2
	A	B	C	D	E	F	G
2
3
4	GL Cashbook 1234				1765		0
5	Invoice 1234				745		745
6
7	GL Cashbook 345				1765		0
8	Invoice 678				745		745
9	Credit Note CN1222				16285
Sheet1

Cell Formulas
Range	Formula
G4:G9	G4	=IF(ISNUMBER(SEARCH("cashbook",A4)),0,IF(ISNUMBER(SEARCH("invoice",A4)),E4,""))

 

[HvdH]

you need isnumber()
=IF(ISNUMBER(SEARCH("cashbook",A4)),0,IF(ISNUMBER(SEARCH("invoice",A4)),E4,""))

Book2
	A	B	C	D	E	F	G
2
3
4	GL Cashbook 1234				1765		0
5	Invoice 1234				745		745
6
7	GL Cashbook 345				1765		0
8	Invoice 678				745		745
9	Credit Note CN1222				16285
Sheet1

Cell Formulas
Range	Formula
G4:G9	G4	=IF(ISNUMBER(SEARCH("cashbook",A4)),0,IF(ISNUMBER(SEARCH("invoice",A4)),E4,""))

Awesome, thank you

 

[etaf]

you are welcome