If values in Column C = specific text, insert specific text into a value in Column G

[john5599]

Sir I have an excel sheet with 4000 rows like below, I would like to add some text in Col. as in sample chart. If column C Value start Bill No.... then I need Column G as invoice.
How it possible with Formula and Macro.
I have a Listed of Text Add in col G.

List1

Invoice

Sales Return

Cash Transfer

Discount

Cash Received

Cash Received

<tbody>
</tbody>

Excel 2007

	A	B	C	D	E	F	G
2	Date	Customer Name	Narration	Amount	Cash Recived	Cheque Recived
3	05/12/2012	Madan Pura	Bill No.1565	15000			Invoice
4	05/12/2012	Jerry Pvt. Ltd	Bill No.1456	10000			Invoice
5	05/12/2012	Jackson Pvt. Ltd	Bill No.1458	25000			Invoice
6	05/12/2012	Jackson Pvt. Ltd	Cash Received		25000		Cash Received
7	18/12/2012	Jackson Pvt. Ltd	Cheque Received			1800	Cheque Received
8	05/12/2013	Varun Associates	Cash Received By		5000		Cash Received
9	05/12/2012	George Peter Groups	Bill No.1780	10300			Invoice
10	05/12/2012	Vikram Associates	Bill No.1863	1050			Invoice
11	15/05/2012	Jerry Pvt. Ltd	Bill No.1834	1000			Invoice
12	15/05/2012	Rupa & Company	Bill No.1865	750			Invoice
13	15/05/2012	Mruthula & Co.	Bill No.1890	400			Invoice
14	16/05/2012	Rupa & Company	Cash Transfer to Bank A/C		7000		Cash Transfer
15	15/05/2012	Varun Associates	Bill No.1925	700			Invoice
16	15/05/2012	Rupa & Company	Bill No.1896	8000			Invoice
17	12/06/2012	Jackson Pvt. Ltd	Sales return		1800		Sales Return
18	25/07/2012	Jackson Pvt. Ltd	Cash Transfer		3600		Cash Transfer
19	18/08/2012	Jackson Pvt. Ltd	Goods Return		360		Sales Return
20	18/05/2013	Jackson Pvt. Ltd	Discount Given		350		Discount Given
21	20/05/2013	Mahesh & Sons	Cash T/F to Bank		1200		Cash Transfer
22	20/05/2013	George Peter Groups	Cheque Received			12000	Cheque Received
23	10/06/2013	George Peter Groups	Goods Return		985		Sales Return

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[bschwartz]

For formula try:

=IF(FIND("Bill No.",C2)=1,"Invoice","")

 

[konew1]

I think this is what you intend, but you will need to ne consistent with data entry in column C. Entries 14 and 21 should be the same but one is entered with abbreviation.

Excel Workbook
	A	B	C	D	E	F	G	H	I
2	Bill No	Bill No	Invoice
3	Sales r	Sales return	Sales Return
4	Cash Tr	Cash Transfer to Bank A/C	Cash Transfer
5	Discoun	Discount Given	Discount
6	Cash Re	Cash Received	Cash Received
7	Cheque	Cheque Received	Cheque Received
8
9
10
11	A	B	C	D	E	F	G
12	Date	Customer Name	Narration	Amount	Cash Recived	Cheque Recived
13	5/12/2012	Madan Pura	Bill No.1565	15000			Invoice	Invoice
14	5/12/2012	Jerry Pvt. Ltd	Bill No.1456	10000			Invoice	Invoice
15	5/12/2012	Jackson Pvt. Ltd	Bill No.1458	25000			Invoice	Invoice
16	5/12/2012	Jackson Pvt. Ltd	Cash Received		25000		Cash Received	Cash Received
Sheet18

 

[john5599]

Sir, First I am giving a great thanks for your kind help.
Sir I have one doubt, If possible, Can you explain the formula.,$A$2:$C$7,3,FALSE. What is meaning of 3 in the formula.

Regards

 

[john5599]

Thank You Sir

 

[konew1]

Sir, First I am giving a great thanks for your kind help.
Sir I have one doubt, If possible, Can you explain the formula.,$A$2:$C$7,3,FALSE. What is meaning of 3 in the formula.

Regards

Thanks for your comments.

The VLOOKUP function H13 =VLOOKUP(LEFT(C13,7),$A$2:$C$7,3,FALSE) has 4 parameters.
In this example, the first parameter is the lookup value. Here it is LEFT(C13,7) that returns the first 7 letters of the description in C13.
The second parameter (A2:C7) specifies a range where to look. The function will find the lookup value in the first column of the range.
The third parameter (3) refers to the third column of the lookup range.
The last parameter (FALSE) is required because the lookup range is not sorted and you want an exact match in the first column, not an approximate match.
The function will find the lookup value in column A and then return the contents of the 3rd column of the range (column C) of the same row.

I had to use LEFT function because all of the invoice lines start "Bill No" then end with a different number.
Because you have cash received and cash transfer the LEFT returns 7 characters.

 

[john5599]

Thanks for your comments.

The VLOOKUP function H13 =VLOOKUP(LEFT(C13,7),$A$2:$C$7,3,FALSE) has 4 parameters.
In this example, the first parameter is the lookup value. Here it is LEFT(C13,7) that returns the first 7 letters of the description in C13.
The second parameter (A2:C7) specifies a range where to look. The function will find the lookup value in the first column of the range.
The third parameter (3) refers to the third column of the lookup range.
The last parameter (FALSE) is required because the lookup range is not sorted and you want an exact match in the first column, not an approximate match.
The function will find the lookup value in column A and then return the contents of the 3rd column of the range (column C) of the same row.

I had to use LEFT function because all of the invoice lines start "Bill No" then end with a different number.

Because you have cash received and cash transfer the LEFT returns 7 characters.

Thank you so much your kind explanation Sir.