Ask Your Question
0

Using VLOOKUP provides false information [closed]

asked 2014-10-31 19:55:24 +0200

lhiggie gravatar image

updated 2016-03-05 18:55:43 +0200

Alex Kemp gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-05 18:56:06.695394

1 Answer

Sort by » oldest newest most voted
2

answered 2014-10-31 23:35:04 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more

Comments

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

JohnSUN gravatar imageJohnSUN ( 2014-11-01 10:18:35 +0200 )edit

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.

blindape gravatar imageblindape ( 2014-11-03 02:13:12 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-10-31 19:55:24 +0200

Seen: 616 times

Last updated: Oct 31 '14