Excel to find key words and paste the same in Criteria column

[tinferns]

Team.. Need your help again. I believe it is possible for Excel to find key words as described by user and paste the same in the dedicated cell. And I also believe this can be done without using a Macro. Please help out on the below.. I prefer not to use Macro's if possible please.

Keyword and their expected output:

Key Words (NOT case sensitive): (This is just an example Key Word list.. actual one is approx. 20 items/values)	Result
Flight	Flight
Flights	Flight
Ticket	Air Ticket
Air Ticket	Air Ticket
Air Tickets	Air Ticket
PNR	PNR
PNR's	PNR
PNRs	PNR

Below is an example (In some cases there are 2 key words used, Excel can choose the first key word in such cases)

Description	Criteria		Expected Result
ABC bought a flight ticket paying XXX amount			Flight
XYZ bought 3 Air Tickets paying xxx amount			Air Ticket
CDE bought 1 Air Ticket vide PNR - XXXXXXX			Air Ticket

Hope to hear from you soon. Thanks.. M

 

[tinferns]

Thanks a trillion Peter SSs. The above formula worked. And it worked just fine. Am using the Lookup and Search option. Thanks again

@ GraH - Sorry I guess my explanation was not so clear as to what I wanted. Thanks sooooo much for your effort. Much appreciated.

Thanks,

M

 

[Peter_SSs]

Thanks a trillion Peter SSs. The above formula worked. And it worked just fine. Am using the Lookup and Search option. Thanks again

You're welcome. Glad it helped.

For clarification in case it hasn't been obvious, the lookup/search formula returns the last matched value from the lookup table, not the last matched value in the searched text.
Example, both of these return "Air Ticket" even though "flight" and "ticket" appear in different order in A13 and A14.

20 10 04.xlsm
	A	B	C	D
1	Key Words	Result
2	Flight	Flight
3	Flights	Flight
4	Ticket	Air Ticket
5	Air Ticket	Air Ticket
6	Air Tickets	Air Ticket
7	PNR	PNR
8	PNR's	PNR
9	PNRs	PNR
10
11
12	Description	Criteria		Last Key Word
13	ABC bought a flight ticket paying XXX amount			Air Ticket
14	ABC bought a ticket on a flight			Air Ticket
Key Words

Cell Formulas
Range	Formula
D13:D14	D13	=LOOKUP(9.99E+307,SEARCH(" "&$A$2:$A$9&" "," "&A13&" "),$B$2:$B$9)

 

[tinferns]

Thanks Peter SSs.. Yes, I noticed that this formula captures the last matched value. Can that be changed to First Matched value instead ? Please advise.

Thanks,

M

 

[Peter_SSs]

Can that be changed to First Matched value instead ?

Not entirely clear what you mean by first. In row 14 of my data in post 12, which result do you want?
a) Air Ticket because ticket comes before flight in cell A14, or
b) Flight because Flight comes before Ticket in the range A2:A9

 

[tinferns]

Hello Peter SSs .. i think the below examples will make this clear:

1) ABC bought a flight ticket paying XXX amount - EXPECTED RESULT - FLIGHT (NOT AIR TICKET)
2) XYZ bought 3 Air Tickets paying xxx amount - EXPECTED RESULT - AIR TICKET
3) CDE bought 1 Air Ticket vide PNR - XXXXXXX - EXPECTED RESULT - AIR TICKET (NOT PNR) \

Thanks

M

 

[Pebblefromheaven]

Search any word in a text(AutoRecovered).xlsx
	G	H	I
4	Description		Result
5	ABC bought a flight ticket paying XXX amount		flight
6	XYZ bought 3 Air Tickets paying xxx amount		Air Ticket
7	CDE bought 1 Air Ticket vide PNR - XXXXX		Air Ticket
Sheet1

Cell Formulas
Range	Formula
I5:I7	I5	=IF(AND(IFERROR(SEARCH("pnr",G5),0)<>0,AND(IFERROR(SEARCH("pnr",G5),0)<=IF(IFERROR(SEARCH("flight",G5),0)<>0,SEARCH("flight",G5),IFERROR(SEARCH("pnr",G5),0)),IFERROR(SEARCH("pnr",G5),0)<=IF(IFERROR(SEARCH("air ticket",G5),0)<>0,SEARCH("air ticket",G5),IFERROR(SEARCH("pnr",G5),0)))),"PNR",IF(AND(IFERROR(SEARCH("flight",G5),0)<>0,AND(IFERROR(SEARCH("flight",G5),1)<=IF(IFERROR(SEARCH("pnr",G5),0)<>0,SEARCH("pnr",G5),IFERROR(SEARCH("flight",G5),0)),IFERROR(SEARCH("flight",G5),1)<=IF(IFERROR(SEARCH("air ticket",G5),0)<>0,SEARCH("air ticket",G5),IFERROR(SEARCH("flight",G5),0)))),"flight",IF(AND(IFERROR(SEARCH("air ticket",G5),0)<>0,AND(IFERROR(SEARCH("air ticket",G5),1)<=IF(IFERROR(SEARCH("pnr",G5),0)<>0,SEARCH("pnr",G5),IFERROR(SEARCH("air ticket",G5),0)),IFERROR(SEARCH("flight",G5),1)<=IF(IFERROR(SEARCH("flight",G5),0)<>0,SEARCH("flight",G5),IFERROR(SEARCH("air ticket",G5),0)))),"Air Ticket")))

 

[GraH]

@ GraH - Sorry I guess my explanation was not so clear as to what I wanted. Thanks sooooo much for your effort. Much appreciated.

Thanks,

M

Hey, no worries. I have a patent on missunderstanding threads . In the process learned something myself. So all is good.

 

[Peter_SSs]

i think the below examples will make this clear:

OK, so you want option a) from my choices.

Try this.

tinferns 1.xlsm
	A	B
1	Key Words	Result
2	Flight	Flight
3	Flights	Flight
4	Ticket	Air Ticket
5	Air Ticket	Air Ticket
6	Air Tickets	Air Ticket
7	PNR	PNR
8	PNR's	PNR
9	PNRs	PNR
10
11
12	Description	Result
13	ABC bought a flight ticket paying XXX amount	Flight
14	XYZ bought 3 Air Tickets paying xxx amount	Air Ticket
15	CDE bought 1 Air Ticket vide PNR - XXXXXXX	Air Ticket
16	ABC bought a ticket on a flight	Air Ticket
17	CDE bought 1 Air Ticket vide PNR - XXXXXXX	Air Ticket
18	CDE bought 1 Air voucher vide PNR on a flight	PNR
Sheet1

Cell Formulas
Range	Formula
B13:B18	B13	=INDEX(B$2:B$9,MATCH(1,SEARCH(A$2:A$9&" ",REPLACE(A13,1,AGGREGATE(15,6,SEARCH(" "&A$2:A$9&" "," "&A13&" "),1)-1,"")&" "),0))

 

[tinferns]

Thanks Peter SSs.. that was magnificent.. Since we are on this ... I have another question and need your help on this.

Would there be a formula to extract either a Number or Number% or Date given either before or after a key word?

Key Word	Actual Sentence​	Result
Discount	I got a 30% Discount on the new bed I bought	30%
Costs	I want to buy a car which costs Rs. 300000.00	300000.00
Date	I want this job to be completed by Date: 25Dec20	25Dec20

Above are just examples. Actual text is similar to the above.

Await your response. .

Thanks,

M

 

[GraH]

Could you have more then one number, a combo of number, percent and, or date in a single sentence?