sayankolay
New Member
- Joined
- Feb 28, 2016
- Messages
- 5
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).
[TABLE="width: 725"]
<colgroup><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD]Search Strings[/TD]
[TD]Search String 2[/TD]
[TD]Search String 3[/TD]
[TD]Search String 4[/TD]
[TD]Issue Path 1[/TD]
[TD]VDN[/TD]
[/TR]
[TR]
[TD]HP ALM[/TD]
[TD]HP ALM[/TD]
[TD]HP ALM[/TD]
[TD]HP ALM[/TD]
[TD]HP ALM[/TD]
[TD]Other Issues[/TD]
[/TR]
[TR]
[TD]Not Found[/TD]
[TD]Not Found[/TD]
[TD]excel[/TD]
[TD]excel[/TD]
[TD]Microsoft Office[/TD]
[TD]Comp & Printer[/TD]
[/TR]
[TR]
[TD]Not Found[/TD]
[TD]Not Found[/TD]
[TD]website[/TD]
[TD]website[/TD]
[TD]Browser[/TD]
[TD]Comp & Printer[/TD]
[/TR]
[TR]
[TD]Not Found[/TD]
[TD]Not Found[/TD]
[TD]access[/TD]
[TD]access[/TD]
[TD]Access[/TD]
[TD]Comp & Printer
[/TD]
[/TR]
</tbody>[/TABLE]
BW is the bag of words its taking to do this lookup and Sheet1 has the actual tables example:-
[TABLE="width: 507"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]String[/TD]
[TD]Issue Type[/TD]
[TD]VDN[/TD]
[/TR]
[TR]
[TD]Password[/TD]
[TD]Password[/TD]
[TD]Password[/TD]
[/TR]
[TR]
[TD]Outlook[/TD]
[TD]Outlook[/TD]
[TD]Lotus & Email[/TD]
[/TR]
[TR]
[TD]Shared Drive[/TD]
[TD]Shared Drive[/TD]
[TD]Comp & Printer[/TD]
[/TR]
[TR]
[TD]Accessnab[/TD]
[TD]Accessnab[/TD]
[TD]Accessnab[/TD]
[/TR]
[TR]
[TD]VPN[/TD]
[TD]VPN[/TD]
[TD]Comp & Printer[/TD]
[/TR]
[TR]
[TD]Lotus Notes[/TD]
[TD]Lotus Notes[/TD]
[TD]Lotus & Email[/TD]
[/TR]
[TR]
[TD]Siebel[/TD]
[TD]Siebel[/TD]
[TD]Siebel & ebobs[/TD]
[/TR]
[TR]
[TD]Missing Software[/TD]
[TD]Missing Software[/TD]
[TD]Comp & Printer[/TD]
[/TR]
[TR]
[TD]Webdesktop[/TD]
[TD]Webdesktop[/TD]
[TD]Accessnab[/TD]
[/TR]
[TR]
[TD]Other Issues[/TD]
[TD]Other Issues[/TD]
[TD]Other Issues[/TD]
[/TR]
[TR]
[TD]Email[/TD]
[TD]Email[/TD]
[TD]Lotus & Email[/TD]
[/TR]
[TR]
[TD]iPhone[/TD]
[TD]iPhone[/TD]
[TD]Lotus & Email[/TD]
[/TR]
[TR]
[TD]ebobs[/TD]
[TD]eBOBS[/TD]
[TD]Siebel & ebobs[/TD]
[/TR]
[TR]
[TD]Printer[/TD]
[TD]Printer[/TD]
[TD]Comp & Printer[/TD]
[/TR]
</tbody>[/TABLE]
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).
[TABLE="width: 725"]
<colgroup><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD]Search Strings[/TD]
[TD]Search String 2[/TD]
[TD]Search String 3[/TD]
[TD]Search String 4[/TD]
[TD]Issue Path 1[/TD]
[TD]VDN[/TD]
[/TR]
[TR]
[TD]HP ALM[/TD]
[TD]HP ALM[/TD]
[TD]HP ALM[/TD]
[TD]HP ALM[/TD]
[TD]HP ALM[/TD]
[TD]Other Issues[/TD]
[/TR]
[TR]
[TD]Not Found[/TD]
[TD]Not Found[/TD]
[TD]excel[/TD]
[TD]excel[/TD]
[TD]Microsoft Office[/TD]
[TD]Comp & Printer[/TD]
[/TR]
[TR]
[TD]Not Found[/TD]
[TD]Not Found[/TD]
[TD]website[/TD]
[TD]website[/TD]
[TD]Browser[/TD]
[TD]Comp & Printer[/TD]
[/TR]
[TR]
[TD]Not Found[/TD]
[TD]Not Found[/TD]
[TD]access[/TD]
[TD]access[/TD]
[TD]Access[/TD]
[TD]Comp & Printer
[/TD]
[/TR]
</tbody>[/TABLE]
BW is the bag of words its taking to do this lookup and Sheet1 has the actual tables example:-
[TABLE="width: 507"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]String[/TD]
[TD]Issue Type[/TD]
[TD]VDN[/TD]
[/TR]
[TR]
[TD]Password[/TD]
[TD]Password[/TD]
[TD]Password[/TD]
[/TR]
[TR]
[TD]Outlook[/TD]
[TD]Outlook[/TD]
[TD]Lotus & Email[/TD]
[/TR]
[TR]
[TD]Shared Drive[/TD]
[TD]Shared Drive[/TD]
[TD]Comp & Printer[/TD]
[/TR]
[TR]
[TD]Accessnab[/TD]
[TD]Accessnab[/TD]
[TD]Accessnab[/TD]
[/TR]
[TR]
[TD]VPN[/TD]
[TD]VPN[/TD]
[TD]Comp & Printer[/TD]
[/TR]
[TR]
[TD]Lotus Notes[/TD]
[TD]Lotus Notes[/TD]
[TD]Lotus & Email[/TD]
[/TR]
[TR]
[TD]Siebel[/TD]
[TD]Siebel[/TD]
[TD]Siebel & ebobs[/TD]
[/TR]
[TR]
[TD]Missing Software[/TD]
[TD]Missing Software[/TD]
[TD]Comp & Printer[/TD]
[/TR]
[TR]
[TD]Webdesktop[/TD]
[TD]Webdesktop[/TD]
[TD]Accessnab[/TD]
[/TR]
[TR]
[TD]Other Issues[/TD]
[TD]Other Issues[/TD]
[TD]Other Issues[/TD]
[/TR]
[TR]
[TD]Email[/TD]
[TD]Email[/TD]
[TD]Lotus & Email[/TD]
[/TR]
[TR]
[TD]iPhone[/TD]
[TD]iPhone[/TD]
[TD]Lotus & Email[/TD]
[/TR]
[TR]
[TD]ebobs[/TD]
[TD]eBOBS[/TD]
[TD]Siebel & ebobs[/TD]
[/TR]
[TR]
[TD]Printer[/TD]
[TD]Printer[/TD]
[TD]Comp & Printer[/TD]
[/TR]
</tbody>[/TABLE]