Index match Search

[dwddavid]

In Column G, I have got sentences for example
G1 I like Bananas
G2 I like Apples
G3 I like Grapes

In Column M, I have got the following formula
=INDEX($T$1:$T$300,MATCH("*"&LOOKUP(9.99999999999999E+307,SEARCH(List,G1),List)&"*",$S$1:$S$300,0))
This formula is filled down to last row of data of Column G. The 300 number represents the number of rows in S.

In Column S, I have types of fruits for example
S1 Apples,grapefruit
S2 Grapes,tomatos
S3 Bananana

In Column T, I have values for example
T1 1
T2 2
T3 3

In a separate sheet (same workbook), I have all possible fruits in Column E. I have defined the list name as list.

For the formula in Column M to work correctly, I would like to search column G for a fruit, Lookup that fruit in Column S and than return whatever value is in the same row for Column T.

The return for M1 should be 3
The return for M2 should be 1
The return for M3 should be 2

For some reason either a 1 or #N/A shows up in the M Column. Does this make sense?

 

[Aladin Akyurek]

I think...

=INDEX($T$1:$T$3,MATCH("*"&LOOKUP(9.99999999999999E+307,SEARCH(List,G1),List)&"*",$S$1:$S$3,0))

should work as desired. If #N/A obtains, we have to conclude that MATCH fails to find a match for the LOOKUP outcome it uses as look up value.

 

[dwddavid]

Could I have your email address so I can email you my file? I found out the formula for the fruit scenario works fine but not my real life example. Not sure what is wrong.

 

[dwddavid]

I have the following in G26
Synopsis :\n\nIt CVE-2010-1138 is possible to obtain the names of processes listening on the\nrem

S1 has:
<TABLE style="WIDTH: 247pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=329><COLGROUP><COL style="WIDTH: 247pt; mso-width-source: userset; mso-width-alt: 12032" width=329><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 247pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=329>CVE-2008-4029,CVE-2008-4033,CVE-2007-0099</TD></TR></TBODY></TABLE>
S2 has:
<TABLE style="WIDTH: 247pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=329><COLGROUP><COL style="WIDTH: 247pt; mso-width-source: userset; mso-width-alt: 12032" width=329><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 247pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=329>CVE-2008-4256,CVE-2008-3704,CVE-2008-4253,CVE-2008-4255,CVE-2008-4254,CVE-2008-4252
S3 has:
<TABLE style="WIDTH: 247pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=329><COLGROUP><COL style="WIDTH: 247pt; mso-width-source: userset; mso-width-alt: 12032" width=329><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 247pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=329>CVE-2010-1138

T1 has:
1
T2 has:
2
T3 has:
3

I would expect the formula in M26 to return a 3 but it returns a 1.

(CVE-2010-1138 is within the defined list.)

</TD></TR></TBODY></TABLE>
</TD></TR></TBODY></TABLE>

 

[Aladin Akyurek]

I have the following in G26
Synopsis :\n\nIt CVE-2010-1138 is possible to obtain the names of processes listening on the\nrem

S1 has:
<TABLE style="WIDTH: 247pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=329><COLGROUP><COL style="WIDTH: 247pt; mso-width-source: userset; mso-width-alt: 12032" width=329><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 247pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=329>CVE-2008-4029,CVE-2008-4033,CVE-2007-0099</TD></TR></TBODY></TABLE>
S2 has:
<TABLE style="WIDTH: 247pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=329><COLGROUP><COL style="WIDTH: 247pt; mso-width-source: userset; mso-width-alt: 12032" width=329><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 247pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=329>CVE-2008-4256,CVE-2008-3704,CVE-2008-4253,CVE-2008-4255,CVE-2008-4254,CVE-2008-4252
S3 has:
<TABLE style="WIDTH: 247pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=329><COLGROUP><COL style="WIDTH: 247pt; mso-width-source: userset; mso-width-alt: 12032" width=329><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 247pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=329>CVE-2010-1138

T1 has:
1
T2 has:
2
T3 has:
3

I would expect the formula in M26 to return a 3 but it returns a 1.

(CVE-2010-1138 is within the defined list.)


</TD></TR></TBODY></TABLE>

</TD></TR></TBODY></TABLE>

I get 3, but with a small List of course.

Would you try the formula in the following form:

=INDEX($T$1:$T$3,MATCH("*"&LOOKUP(9.99999999999999E+307,SEARCH(","&List&",",","&G1&","),List)&"*",$S$1:$S$3,0))

which attempts to expoit comma's you have between the items in the target strings?

 

[dwddavid]

We are getting closer. I am getting #N/A in places where I expect a value.

 

[Aladin Akyurek]

We are getting closer. I am getting #N/A in places where I expect a value.

Care to post the item that should match and the string against which it fails to match?

 

[dwddavid]

Correctly shows on first formula/ CVE-2011-1978 is in column S and List
Incorrectly shows N/A on 2<SUP>nd</SUP> Formula
Synopsis :\n\nIt is possible to obtain the names of processes CVE-2011-1978 listening on the\nremote UDP and TCP ports.\n\nDescription :\n\nThis script uses WMI to list the processes running on the remote host\nand listening on TCP / UDP ports.\n\nSolution :\n\nn/a\n\nRisk factor :\n\nNone\n\nPlugin output :\nThe Win32 process 'svchost.exe' is listening on this port (pid 1456).\n\nThis process 'svchost.exe' (pid 1456) is hosting the following Windows services :\nFDResPub (@%systemroot%\\system32\\fdrespub.dll,-100)\nFontCache (@%systemroot%\\system32\\FntCache.dll,-100)\nSCardSvr (@%SystemRoot%\\System32\\SCardSvr.dll,-1)\nwcncsvc (@%SystemRoot%\\system32\\wcncsvc.dll,-3)\n\n\n\n
N/A on 2<SUP>nd</SUP> Formula (Should have a value) CVE-2011-1267 is in column S and List
Synopsis :\n\nThe remote Windows host has a denial of service vulnerability.\n\nDescription :\n\nThe remote host is affected by a vulnerability in the SMB service\nthat can reportedly be abused by a remote, unauthenticated attacker to\ncause the host to stop responding until manually restarted. \n\nThis vulnerability depends on access to a Windows file share, but\nmight not necessarily require credentials.\n\nSolution :\n\nMicrosoft has released a set of patches for Vista, 2008, 7, and 2008\nR2 :\n\nhttp://www.microsoft.com/technet/security/Bulletin/MS11-048.mspx\n\nRisk factor :\n\nHigh / CVSS Base Score : 7.8\n(CVSS2#AV:N/AC:L/Au:N/C:N/I:N/A:C)\n\nCVE : CVE-2011-1267\nBID : 48185\nOther references : OSVDB:72936,MSFT:MS11-048\n
N/A on 2<SUP>nd</SUP> Formula (Should have a value) CVE-2011-1266 is in column S and List
Synopsis :\n\nArbitrary code can be executed on the remote host through a web\nbrowser.\n\nDescription :\n\nThe remote host is missing Internet Explorer (IE) Security Update\n2497640. \n\nThe installed version of IE is affected by a vulnerability in the\nimplementation of the Vector Markup Language (VML) that could allow an\nattacker to execute arbitrary code on the remote host.\n\nSee also :\n\nhttp://www.zerodayinitiative.com/advisories/ZDI-11-197/\n\nSolution :\n\nMicrosoft has released a set of patches for XP, 2003, Vista, 2008, 7,\nand 2008 R2 :\n\nhttp://www.microsoft.com/technet/security/bulletin/ms11-052.mspx\n\nRisk factor :\n\nHigh / CVSS Base Score : 9.3\n(CVSS2#AV:N/AC:M/Au:N/C:C/I:C/A:C)\n\n\nPlugin output :\n- C:\\Program Files\\Common Files\\Microsoft Shared\\VGX\\Vgx.dll has not been patched\n Remote version : 8.0.7600.16385\n Should be : 8.0.7600.16806\n\n\n\nCVE : CVE-2011-1266\nBID : 48173\nOther references : OSVDB:72954,MSFT:MS11-052\n

 

[Aladin Akyurek]

Correctly shows on first formula/ CVE-2011-1978 is in column S and List
Incorrectly shows N/A on 2<SUP>nd</SUP> Formula
Synopsis :\n\nIt is possible to obtain the names of processes CVE-2011-1978 listening on the\nremote UDP and TCP ports.\n\nDescription :\n\nThis script uses WMI to list the processes running on the remote host\nand listening on TCP / UDP ports.\n\nSolution :\n\nn/a\n\nRisk factor :\n\nNone\n\nPlugin output :\nThe Win32 process 'svchost.exe' is listening on this port (pid 1456).\n\nThis process 'svchost.exe' (pid 1456) is hosting the following Windows services :\nFDResPub (@%systemroot%\\system32\\fdrespub.dll,-100)\nFontCache (@%systemroot%\\system32\\FntCache.dll,-100)\nSCardSvr (@%SystemRoot%\\System32\\SCardSvr.dll,-1)\nwcncsvc (@%SystemRoot%\\system32\\wcncsvc.dll,-3)\n\n\n\n
N/A on 2<SUP>nd</SUP> Formula (Should have a value) CVE-2011-1267 is in column S and List
Synopsis :\n\nThe remote Windows host has a denial of service vulnerability.\n\nDescription :\n\nThe remote host is affected by a vulnerability in the SMB service\nthat can reportedly be abused by a remote, unauthenticated attacker to\ncause the host to stop responding until manually restarted. \n\nThis vulnerability depends on access to a Windows file share, but\nmight not necessarily require credentials.\n\nSolution :\n\nMicrosoft has released a set of patches for Vista, 2008, 7, and 2008\nR2 :\n\nhttp://www.microsoft.com/technet/security/Bulletin/MS11-048.mspx\n\nRisk factor :\n\nHigh / CVSS Base Score : 7.8\n(CVSS2#AV:N/AC:L/Au:N/C:N/I:N/A:C)\n\nCVE : CVE-2011-1267\nBID : 48185\nOther references : OSVDB:72936,MSFT:MS11-048\n
N/A on 2<SUP>nd</SUP> Formula (Should have a value) CVE-2011-1266 is in column S and List
Synopsis :\n\nArbitrary code can be executed on the remote host through a web\nbrowser.\n\nDescription :\n\nThe remote host is missing Internet Explorer (IE) Security Update\n2497640. \n\nThe installed version of IE is affected by a vulnerability in the\nimplementation of the Vector Markup Language (VML) that could allow an\nattacker to execute arbitrary code on the remote host.\n\nSee also :\n\nhttp://www.zerodayinitiative.com/advisories/ZDI-11-197/\n\nSolution :\n\nMicrosoft has released a set of patches for XP, 2003, Vista, 2008, 7,\nand 2008 R2 :\n\nhttp://www.microsoft.com/technet/security/bulletin/ms11-052.mspx\n\nRisk factor :\n\nHigh / CVSS Base Score : 9.3\n(CVSS2#AV:N/AC:M/Au:N/C:C/I:C/A:C)\n\n\nPlugin output :\n- C:\\Program Files\\Common Files\\Microsoft Shared\\VGX\\Vgx.dll has not been patched\n Remote version : 8.0.7600.16385\n Should be : 8.0.7600.16806\n\n\n\nCVE : CVE-2011-1266\nBID : 48173\nOther references : OSVDB:72954,MSFT:MS11-052\n

I can't replicate the problem...

Care also check this one, which must be confirmed with control+shift+enter:

=INDEX($T$1:$T$3,MATCH("*"&LOOKUP(9.99999999999999E+307,SEARCH(","&List&",",","&G1&","),List)&"*",SUBSTITUTE($S$1:$S$3," ",""),0))

This eliminates spaces in an S-entry.

 

[dwddavid]

I got the first formula working. I had one entry in the data set that was throwing off the formula. Thanks for your help