Using VLOOKUP provides false information

[lhiggie]

Good afternoon,

I have the following set up. I am running Centos 6.5 running LO 4.0.4.2 (Build ID: 4.0.4.2-14.el6). I have 2 sheets in the LO calc. Sheet A contains a table with the following columns:

Node (Column A)
IP Address (Column B)
Functional Name (Column C)
Status (Column D)
Comment 1 (Column E)
Comment 2 (Column F)
Comment 3 (Column G)

I have a Sheet B which has the following columns:

Functional Name (Column A)
Node Name (Column B)
Functional IP (Column C)
Cores (Column D)
G RAM (Column E)
G disk (Column F)
Description (Column G)
Notes (Column H)

I’m am attemping to populate the IP Address in Sheet A (Column B) with the equivalent from Sheet B (Column C). I do have the table in Sheet B sorted in ascending order. Here is my formula:

=IFERROR(IF(A4="","",IF(VLOOKUP(A4,Eagle6_node_table_v_2.$B$4:$H$152,2,1)="",“No IP Assigned”,VLOOKUP(A4,Eagle6_node_table_v_2.$B$4:$H$152,2,1))),“Node does not Exist”)

Which works perfectly with the exception of getting duplicate data. I’m getting the same IP address for multiple Nodes and when I look at Sheet B, there are no duplicates. So, it appears there is a problem with the way VLOOKUP is “looking up” the data in Cell A4 which is a alphanumeric cell such as a000b00c00. A prime example is:

a002b00c06 is 12.34.567.89
a002b00c07 is 12.34.567.90
a002b00c08 is 12.34.567.90
a002b00c09 is 12.34.567.90

The problem is that in SheetB a002b00c08 and a002b00c09 do not exist but yet they get an IP address.

Any help would be wonderful. Thank you all in advance.

Sincerely,
lhiggie

[mariosv]

Please take a look at VLOOKUP() help, I think you need to set up the SortOrder to 0 for avoid a result when the SearchCriterion is not in the list.

=VLOOKUP(SearchCriterion; Array; Index; SortOrder)
SearchCriterion is the value searched for in the first column of the array.
Array is the reference, which is to comprise at least two columns.
Index is the number of the column in the array that contains the value to be returned. The first column has the number 1.
SortOrder is an optional parameter that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE or zero if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return this message: Error: Value Not Available.

[JohnSUN]

… so, verify result of search not IF(VLOOKUP(...,1)="",... but IF(ISERROR(VLOOKUP(...,0)),...

[blindape]

You’ve got it. If you want to ensure that you are exactly matching the SearchCriterion with a value in the lookup Array, always enter 0 for the last argument.