Excel Find

[kmi]

1.Here in "Table-1" the invoice number of 5 branches is given from which only branch name should be filtered through excel formula.
2.As shown in Table-3, the answer should come through Excel formula in Table-2.
3.If Subtotal appears in Table-1, then the branch name should appear in the line above which the invoice number of the branch. is as shown in Table-3.

Sample Answer Given In Table-3 typed manually .Same Answer Should Came In Table-2 through Excel Formula

 

[alansidman]

Cannot manipulate data in a picture. Please reload to this forum using XL2BB so that we don't have to reconstruct your data. See the download at the top of your reply.

 

[hagia_sofia]

Could you please test this:

=SCAN("",IF(ISNUMBER(FIND("CUSTOMER",A7:A41)),"",RIGHT(TEXTBEFORE(A7:A41,"-"),3)),LAMBDA(a,b,IF(ISNA(b),a,b)))

 

[Scott Huish]

Smaller version of your sample, but perhaps:

Book2
	A	B
6	INVOICE NUMBER	Voucher
7	CUSTOMER-1
8	SrVe:2024RWA1-1827	RWA
9	SiVe:2024BRBA1-2417	BRB
10	Sub Total	BRB
Sheet1

Cell Formulas
Range	Formula
B7:B10	B7	=IF(LEFT(A7,8)="CUSTOMER","",IF(A7="Sub Total",B6,MID(A7,10,3)))