Ubuntu
openoffice.org package

LOOKUP not returning #N/A when it should

Asked by Andrew Mercer

I have to take data from one indexed pair of columns and put it into another pair. The second index is not entirely identical to the first in that some post in the first are missing from the second and visa versa.
The first instance of this was handled correctly with lookup returning #N/A but subsequent missing data is replaced by a copy of the contents of the cell above. More oddly, this then seems to perturb the function so that subsequent lookups are out by one row.

Question information

Language:
English Edit question
Status:
Solved
For:
Ubuntu openoffice.org Edit question
Assignee:
No assignee Edit question
Solved by:
Andrew Mercer
Solved:
Last query:
Last reply:
Revision history for this message
marcus aurelius (adbiz) said : 		#1

for the last field, are you using 0 or 1? if you're using 1 (true), it tells calc to find the CLOSEST match. you need to use 0 (false), so it finds the EXACT match.

Revision history for this message
Andrew Mercer (mercergeoinfo) said : 		#2

This is an example from the first few rows of the table:

B C G H
02N1 4.35 02C =LOOKUP(G2,B$2:B$302,C$2:C$302)
02S 4.25 02N1
03C 2.8 03C
03N1 2.3 03N1
03S1 2.85 03S1

Columns B and G contain the keys, column C contains the data to be put into column H.
LOOKUP doesn't seem to take any more parameters.

Revision history for this message
Ted C (tedc) said : 		#3

The 0 & 1 is certainly so in Excel.

The help on Lookup says "If LOOKUP cannot find the search criterion, it matches the largest value in the search vector that is less than or equal to the search criterion.'

And I assume you've got this bit covered -"the search vector for the LOOKUP must be sorted ascending" [same as Excel].

Hang on ... there's a VLOOKUP function -

"VLOOKUP

Vertical search with reference to adjacent cells to the right. This function checks if a specific value is contained in the first column of an array. The function then returns the value in the same row of the column named by Index. If the SortOrder parameter is omitted or set to TRUE or one, it is assumed that the data is sorted in ascending order. In this case, if the exact SearchCriterion is not found, the last value that is smaller than the criterion will be returned. If SortOrder is set to FALSE or zero, an exact match must be found, otherwise the error Error: Value Not Available will be the result. Thus with a value of zero the data does not need to be sorted in ascending order."

The language in the Help is curiously worded but try VLOOKUP - I think it's 'sort order' bit is the extra parameter you are looking for.

I haven't tried it (it's almost midnight here and the head is a bit wooley!)

Hope this helps.
TC

Revision history for this message
Andrew Mercer (mercergeoinfo) said : 		#4

Reading the VLOOKUP help again I realized what I was doing wrong (everything).
My code should have looked like this
=VLOOKUP(G2,B$1:C$302,2,0
where G2 contains the key to look for, B and C contain the key and data being referenced, 2 indicates that the data to be transfered is in the second column of my B and C data range and 0 indicates exact match only.

Quite simple really if you read the instructions. Of course LOOKUP doesn't have the last parameter and my keys are not really in any sort of order, I can't see how they could be meaningfully ordered.