Excel Formula Question (=IF Function)

[TravelMI]

Hi,

I am trying to return values of Invoice or Credit Note in column B based on the first letter or digit from column A. The table below shows what the requirements are (E.G M or 4 = Invoice, 7 = Credit Note. However, I'm stuck with the formula below and unsure of the formula required to allow the first two examples to be inputted with 'Invoice'. Any help appreciated.


=IF( ????? ,"Invoice","Credit Note")

[TABLE="width: 240"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Invoice #[/TD]
[TD]Label[/TD]
[/TR]
[TR]
[TD]M012345[/TD]
[TD]Invoice[/TD]
[/TR]
[TR]
[TD]412345[/TD]
[TD]Invoice[/TD]
[/TR]
[TR]
[TD]712345[/TD]
[TD]Credit Note[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FormR]

Hi, welcome to the forum!

Is it safe to assume that if it's not an "Invoice" then it must be a "credit note"? If so, one option you could try:

Excel 2013
	A	B
1	Invoice #	Label
2	M012345	Invoice
3	412345	Invoice
4	712345	Credit Note
Sheet1

Cell Formulas
Range	Formula
B2	=IF(OR(LEFT(A2)={"M","4"}),"Invoice","Credit Note")

 

[FormR]

(E.G M or 4 = Invoice, 7 = Credit Note.

Or if there is only one option for credit note then it maybe simpler to say.

=IF(LEFT(A2)="7","Credit Note","Invoice")

 

[TravelMI]

Hi,

Thank you for your quick response! Both formula do exactly what I was looking for but you are right with the second option being simpler.

I'll certainly be posting any other queries I have to this forum and try to answer any questions where I can!