Fwd: Re: [libreoffice-users] VLOOKUP for Numbers

Oops, sent this to the original sender, not the list:


Please clarify:

> I haven't had any luck figuring out a method to compare each element 
> of a list of numbers to each element of a differently sized list of 
> numbers.  What I want would bo something very much like VLOOKUP, but 
> instead of matching strings, it would compare a number using an 
> inequality and then return a value from another column along the row 
> where a match was found.

This is confusing.  You say you're looking for an inequality, then 
return a match from another column along the row where the match was 
found.  Are you looking for matches or not?  Do you mean return a value 
along the row where a match WASN'T found (how would you identify the row 
where a match isn't found, since that could be all of the rows in the 
search column)?  Or are you saying if VLOOKUP finds some mathematical 
derivation of the value in the source column (say, the square root), 
then return the value of the row of THAT match?

What VLOOKUP does is return a value from another column where there IS a 
match.  VLOOKUP works on strings or numbers.  Where there IS a match 
with a matching column, it returns the value from the same row of a 
separate column in the lookup array.  If there's no match, it returns a 
blank or N/A.  If what you want is to identify the places where a match 
ISN'T found, couldn't you then use an IF statement to test if vlookup 
returns a value and then return a blank where there's a match and a 
value (say, 1) if the vlookup doesn't find a value?

Note that vlookup works on different sized lists - the column with 
numbers that you want to look for in another column doesn't need to be 
the same length as the matching column.  Also, keep in mind that you'll 
usually need to reference the array list absolutely (use $ before column 
name and row number).  So if you're matching numbers in column A that is 
100 numbers long, the lookup array from B1 to C50 would need to be 
specified as $B$1:$C$50. Otherwise the array range gets adjusted for 
each row down the search column.

If you want a value from another column returned where there ISN'T a 
match, you'll have to specify how the spreadsheet would identify that 
value.  A small spreadsheet might help if you show us what you'd LIKE to 
see returned.  But make sure it's pretty small so we can see any 
patterns easily.

--

Carl Paulsen

Dover, NH




--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted