Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I do multi VLOOKUP's based on certain criteria per cell?
Thank-you for looking at my question!
I suppose I am trying to perform multiple (embedded) VLOOKUP's although there may be another way I can achieve the same result. I have a list of server names (column A down) and a row of patch names (Row 2 along) I have another sheet in my workbook containing patch scan results (hostname_patchname) which I have defined as name 'database' and am looking up if each server has the patch or not. =vlookup($A2&"_"&B$1,database,2,false) This is working a treat, however I need to do this based on whether a patch is applicable to each particular server. I have another data area with all the server names and what operating system they are running and want to only show a result per patch if said patch is relevant to that server. Hope this makes sense to someone without a view of the sheet :-) Many thanks for any help anyone can provide. Scott |
#2
|
|||
|
|||
try something like
if your applicability database is set with Servers in the first column and the Patches in the first row with a applicable or not applicable indicator in the intersection cells =if(Vlookup($A$28,Applicability database,match($B$1,Patch row description),false),vlookup($A2&"_"&B$1,database,2 ,false),"") "Milky_UK" wrote: Thank-you for looking at my question! I suppose I am trying to perform multiple (embedded) VLOOKUP's although there may be another way I can achieve the same result. I have a list of server names (column A down) and a row of patch names (Row 2 along) I have another sheet in my workbook containing patch scan results (hostname_patchname) which I have defined as name 'database' and am looking up if each server has the patch or not. =vlookup($A2&"_"&B$1,database,2,false) This is working a treat, however I need to do this based on whether a patch is applicable to each particular server. I have another data area with all the server names and what operating system they are running and want to only show a result per patch if said patch is relevant to that server. Hope this makes sense to someone without a view of the sheet :-) Many thanks for any help anyone can provide. Scott |
#3
|
|||
|
|||
Many thanks BJ, have not got this to work yet, although only because I have
not yet worked out how to define which OS's each patch supports. Guess I would have to define another name then. Currently I have a few sheets with defined names; 1. Database containing cleansed data that I have exported from a patch vulnerability scanning tool. This data is in the format A B HOST STATUS SERVER_PATCH Installed SERVER_PATCH Not Installed SERVER_PATCH Installed 2. OS_Version that is simply a long list of servers I am interested in and the OS they are running A B HOST OS Server1 W2K Server2 W3K Server3 NT4 (yes, would you credit it, lol) I do actually have another sheet with my Patch list I am reporting on and their compatibility with each OS. A B C D PATCH W2k W3K NT4 Patch1 Yes Yes Yes Patch2 No No Yes Patch3 Yes Yes No I currently run "=vlookup($A2&"_"&B$1,database,2,false" and this works, although gets me a result for everything. My aim is to only get a result if the Server3 running NT4 has or has not got patches Patch1 or Patch2 as Patch3 is not relevant to the NT4 OS. Will continue working with your (BJ) formulae as I do think that you have hit the nail on the head with the MATCH function! Excellent, will let you know if I make any progress, again, many thanks BJ, have a good weekend. |
#4
|
|||
|
|||
Many thanks BJ, have not got this to work yet, although only because I have
not yet worked out how to define which OS's each patch supports. Guess I would have to define another name then. Currently I have a few sheets with defined names; 1. Database containing cleansed data that I have exported from a patch vulnerability scanning tool. This data is in the format A B HOST STATUS SERVER_PATCH Installed SERVER_PATCH Not Installed SERVER_PATCH Installed 2. OS_Version that is simply a long list of servers I am interested in and the OS they are running A B HOST OS Server1 W2K Server2 W3K Server3 NT4 (yes, would you credit it, lol) I do actually have another sheet with my Patch list I am reporting on and their compatibility with each OS. A B C D PATCH W2k W3K NT4 Patch1 Yes Yes Yes Patch2 No No Yes Patch3 Yes Yes No I currently run "=vlookup($A2&"_"&B$1,database,2,false" and this works, although gets me a result for everything. My aim is to only get a result if the Server3 running NT4 has or has not got patches Patch1 or Patch2 as Patch3 is not relevant to the NT4 OS. Will continue working with your (BJ) formulae as I do think that you have hit the nail on the head with the MATCH function! Excellent, will let you know if I make any progress, again, many thanks BJ, have a good weekend. "bj" wrote: try something like =if(Vlookup($A$28,Applicability database,match($B$1,Patch row description),false),vlookup($A2&"_"&B$1,database,2 ,false),"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtract a group of cells from a total based on ending date | Excel Discussion (Misc queries) | |||
Can I select a worksheet based upon a cell criteria?(for printing) | Excel Worksheet Functions | |||
Can I count in an array based on a function? | Excel Worksheet Functions | |||
changing value of a cell based on another cell color | Excel Discussion (Misc queries) | |||
Dynamic Column VlookUps Based on Week Number | Excel Worksheet Functions |