Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index


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

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.