VLOOKUP not displaying anything-CodePudding

I'm trying to compare two servers list to see which servers has not agent installed. On the Row A are all the servers that we have (725 cells) and on Row B we have (540 cells) servers that has agent installed. Lastly on the row C I would want to display missing servers that has not agent yet.

I'm trying following formula on the Row C:

=IF(ISERROR(VLOOKUP(A2,$B$2:$A$725,1,FALSE)),A2, "")

but nothing seems to happen

What am I now missing here so that this is not working for me?

Running Excel-365 and with following version

CodePudding user response：

You can use FILTER() function to get list of servers, agents are not installed.

=FILTER(A2:A4,COUNTIF(B2:B725,A2:A4)=0)

If you want list to rows for not installed servers then can use-

=IF(NOT(ISNUMBER(MATCH(A2,B2:$B$725,0))),A2,"")

Or BYROW() for array approach-

=BYROW(A2:A4,LAMBDA(x,IF(ISERROR(XMATCH(x,B2:B725)),x,"")))

CodePudding user response：

One thing I don't understand, is the fact that you are using VLookup(...,...,1,...): as far as I understand VLookup() is used for following case:

a    b    c
1   10  100
2   20  200
3   30  300

... and you want to find the value in the 'c' column, corresponding with value 2 in column 'a'. In that case you are looking for value 2 and you look in the third column, something like VLookup(...,...,3,...).

However, if you're just looking for that value itself, instead of using VLookup(lookup_value,range,1,exact_match), you can use Match(lookup_value,range,exact_match).

CodePudding user response：

You have a typo. Replace:

=IF(ISERROR(VLOOKUP(A2,$B$2:$A$725,1,FALSE)),A2, "")

with:

=IF(ISERROR(VLOOKUP(A2,$B$2:$B$725,1,FALSE)),A2, "")

The range should be only the B column.