CODEWITHSUNDEEP
excelexcel-formulavlookup
dcraven
dcraven

Reputation: 139

Excel VLOOKUP required to filter data set

I am required to perform a check if all server drives are being monitored.

I have two sources of information:

  1. A search that includes all Servers, Drives & Paths (Query)
  2. A search that includes Servers & Drives being monitored (Raw Data)

To make this check easier I have concatenated the server and drives from both sources of data.

_

Screenshot

Additionally I have used =ISNUMBER(FIND("Hosting",D2))to check if the Path column contains the word 'Hosting'.

If the word 'Hosting' is contained in the Path for a drive that is not listed in the F column then this needs highlighted as it signifies that it is not being monitored.

The data within C8 should be highlighted in some way.

Upvotes: 0

Views: 980

Answers (2)

QHarr
QHarr

Reputation: 84465

Conditional formatting using the following rule? Assumes where "hosting" found in a path you are then searching all of F for a match and if not found then highlight.

=ISERROR(IF(ISNUMBER(FIND("Hosting",D2)),IF(MATCH(C2,F:F,0)>0,"TRUE","FALSE"),"FALSE"))

If you want to return "Monitored" | "Not Monitored" a lazy way would be:

=IF(ISERROR(IF(ISNUMBER(FIND("Hosting",D2)),IF(MATCH(C2,F:F,0)>0,"TRUE","FALSE"),"FALSE")),"Not monitored", "Monitored")

You could set conditional formatting then on a word match.

Upvotes: 1

GalAbra
GalAbra

Reputation: 5148

If the structure of the cells in column F is consistent and is (server-name),(driver letter) for all rows in column D, then I'd create an additional column to check your condition.

The formula in the new column would be: =IF(AND(E1, B1<>RIGHT(F1,1)), TRUE, FALSE).

Then just make a Conditional Formatting rule for the additional column.

Upvotes: 1

Related Questions