FORMULA FOR CATEGORIES

[punit83]

NEED FORMULA TO CATEGORIES FROM DATA.

IN COLUMN A DATA ARE THERE WITH THIS DETAIL IN IT AND I WANT TO CATEGORIES IN COLUMN B.
PLS HELP WITH FORMULA I TRIED WITH IFS, SEARCH BUT GETTING ERROR.

Book1
	A	B
1	INV AB 001-24-25	INV
2	PAYMENT RECEIVED	PAYMENT
3	RECEIVED MATERIAL	MATERIAL
4	PART PAYMENT	PART
Sheet1

THANKS IN ADVANCE.

 

[Beneindias]

Well,

I'm not sure if I understood what you were asking, but I think that this is it:

=IF(LEFT(A1;3)="INV";"INV";IF(A1="PAYMENT RECEIVED";"PAYMENT";IF(A1="RECEIVED MATERIAL";"MATERIAL";IF(A1="PART PAYMENT";"PART"))))

Maybe some excel guru knows a better way, this is my way...

 

[Scott Huish]

How about:
=XLOOKUP(LEFT(A1,3),{"INV","PAY","REC","PAR"},{"INV","PAYMENT","MATERIAL","PART"},"")

 

[punit83]

Well,

I'm not sure if I understood what you were asking, but I think that this is it:

=IF(LEFT(A1;3)="INV";"INV";IF(A1="PAYMENT RECEIVED";"PAYMENT";IF(A1="RECEIVED MATERIAL";"MATERIAL";IF(A1="PART PAYMENT";"PART"))))

Maybe some excel guru knows a better way, this is my way...

Hello Thanks for reply with formula.... but will not work for me
Please refer below table maybe i couldn't explain properly in above query.

Sorry for above improper data

 

[punit83]

How about:
=XLOOKUP(LEFT(A1,3),{"INV","PAY","REC","PAR"},{"INV","PAYMENT","MATERIAL","PART"},"")

Thank you for reply.
maybe i couldn't explain properly in above query, please refer to below table as i need formula for below table to extract data and create categories,
Sorry for above improper data

Please refer to below table

Book1.xlsx
	A	B
1	INV AB 001-24-25 Dated :XX-XX-XXXX	INV
2	PAYMENT RECEIVED FOR INV # AB	PAYMENT
3	RECEIVED MATERIAL FOR PROJECT X	MATERIAL
4	PART PAYMENT	PART
Sheet1

 

[Cubist]

I don't think your criteria are clear. What do expect to return for
"PARTIAL PAYMENT FOR MATERIAL INV XYZ" ?
"INV PAYMENT FOR XYZ"?

 

[punit83]

I don't think your criteria are clear. What do expect to return for "PARTIAL PAYMENT FOR MATERIAL XYZ" ?

if column A data has INV word in it "INV AB 001-24-25 Dated :XX-XX-XXXX" than i want to categorized as "INV" in column B
if column A data has PAYMNET word in it "PAYMENT RECEIVED FOR INV # AB" than i want to categorized as "PAYMENT" in column B
if column A data has MATERIAL word in it "RECEIVED MATERIAL FOR PROJECT X" than i want to categorized as "MATERIAL" in column B
if column A data has PART word in it "RECEIVED PART PAYMENT FOR INV # 123" than i want to categorized as "PART" in column B


Thanx in Advance

 

[Cubist]

The point of my prior post was multiple keywords can appear within the same string. Do you want to categorize whichever appear first?

 

[punit83]

if column A data has INV word in it "INV AB 001-24-25 Dated :XX-XX-XXXX" than i want to categorized as "INV" in column B
if column A data has PAYMNET word in it "PAYMENT RECEIVED FOR INV # AB" than i want to categorized as "PAYMENT" in column B
if column A data has MATERIAL word in it "RECEIVED MATERIAL FOR PROJECT X" than i want to categorized as "MATERIAL" in column B
if column A data has PART word in it "RECEIVED PART PAYMENT FOR INV # 123" than i want to categorized as "PART" in column B


Thanx in Advance

.....YES

 

[Cubist]

Try:

Book1
	A	B	C	D
1	INV AB 001-24-25 Dated :XX-XX-XXXX	INV		INV
2	PAYMENT RECEIVED FOR INV # AB	PAYMENT		PAYMENT
3	RECEIVED MATERIAL FOR PROJECT X	MATERIAL		MATERIAL
4	PART PAYMENT	PART		PART
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=LET(s,SEARCH($D$1:$D$4,A1),XLOOKUP(MIN(IF(ISNUMBER(s),s)),s,$D$1:$D$4))