Help with Lookup Formula - LOOKUP(1000

[sayankolay]

Hi Excel Experts,

I am trying to decipher the below mentioned formula but unable to understand the function of " Lookup(1000," !!!

I know in the lookup formula it is the lookup value but the sheet i am working on is text only & in an unstructured format. It helps to categorize different product categories into main & sub category based on text search & i am trying to reverse engineer it to fit my project requirements.

Any help would be highly appreciated

The complete formula is
=LOOKUP(REPT("z",255),CHOOSE({1,2},"Not Found",LOOKUP(1000,SEARCH(Sheet1!$B$2:$B$23,Dump!BW2),Sheet1!$B$2:$B$23)))

& its is resulting into this table by looking up details from next sheet (Sheet1).

Search Strings	Search String 2	Search String 3	Search String 4	Issue Path 1	VDN
HP ALM	HP ALM	HP ALM	HP ALM	HP ALM	Other Issues
Not Found	Not Found	excel	excel	Microsoft Office	Comp & Printer
Not Found	Not Found	website	website	Browser	Comp & Printer
Not Found	Not Found	access	access	Access	Comp & Printer

<colgroup><col><col span="3"><col><col></colgroup><tbody>
</tbody>

BW is the bag of words its taking to do this lookup and Sheet1 has the actual tables example:-

String	Issue Type	VDN
Password	Password	Password
Outlook	Outlook	Lotus & Email
Shared Drive	Shared Drive	Comp & Printer
Accessnab	Accessnab	Accessnab
VPN	VPN	Comp & Printer
Lotus Notes	Lotus Notes	Lotus & Email
Siebel	Siebel	Siebel & ebobs
Missing Software	Missing Software	Comp & Printer
Webdesktop	Webdesktop	Accessnab
Other Issues	Other Issues	Other Issues
Email	Email	Lotus & Email
iPhone	iPhone	Lotus & Email
ebobs	eBOBS	Siebel & ebobs
Printer	Printer	Comp & Printer

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>