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

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

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.

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),"")