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


Hello,
An answer your V/HLOOKUP question:
HLOOKUP searches for a value on a row (Horizontally) and then goes down
to fetch the corresponding entry on a lower rowVLOOKUP searches for a
value in a column (Vertically) and then goes across to fetch the
corresponding entry on a column to the right
In the case of your data, it seems to be placed in rows, so HLOOKUP
would be the function to use, which is what your test shows.
The way I would get the two entries you are looking for is:
Maximum
value: =MAXIFS(A1:H1,A1:H1,CONCAT("<";DATE(2000,1,1)))Corresponding
date value: =INDIRECT(ADDRESS(LINE(A1),MATCH(maximum_value,A1:H1,0)+1))
I made the assumption your dates are on or after Jan 1, 2000 and that
the values will be less than 36526 (equivalent date value of Jan 1,
2000): this allows me to find the max for the entire row at once
without thinking about where the dates and values are. Then, I use
MATCH to find where the value is, which feeds the INDIRECT/ADDRESS pair
to extract the value. Note that this strategy will find the first
maximum value if the values can be identical.
If the date values are always text values, you could find the max on
the row with this formula (replace -99999 with any small enough
number):{=MAX(IF(ISNUMBER(A1:H1),A1:H1,-99999))}
Notice the curly braces ('{" and "}"): this means it is an array
formula and is entered by typing:
=MAX(IF(ISNUMBER(A1:H1),A1:H1,-99999)) followed by CTRL-SHIFT-Enter
I hope this helps.
Rémy.

Le mardi 15 septembre 2020 à 13:50 +0200, Hylton Conacher (ZR1HPC) a
écrit :
HI,
USING
I have eight data values on a single row with corresponding dates
next to each value in the format below i.e.
A     B       C       D       E       F       G       H25     01/07   blank
      60      09/05   blank   30      26/7
In the 2nd row I would like to show the highest number from the first
row as well as the corresponding date value in the cell to the right
of that value.
My A2 formula is MAX(A2,D2) = 60
The issue I am having is to get the corresponding date value from the
adjacent column i.e. 09/05 in row B2.
The formula I have tried include:=VLOOKUP(MAX(A1,D1,G1),B1:H1,FALSE)
= #N/A=MATCH(INDEX(A2),A1:H1,0) =
#N/A=HLOOKUP(MAX(A1,D1,G1),A1:H1,1,0)  =60 BEING THE SAME VALUE AS
MAX().
I figured that as VLOOKUP looks for values below that HLOOKUP might
be better suited. That said I have always battled understanding the
terminology for the requirements for both vlookup and hlookup, even
Index and Match needs understanding I don't have.
I read the LO manual online and Google has been some help but seems
to always go the VLOOKUP route with all the data under each other.
Unfortunately this sort of 'table' VLOOKUP requires is not feasible
on my data set.
I should probably use BASE but have not delved into that especially
as the data making up these figures is graphed.
Anyone's guidance appreciated.
RegardsHyltonmailto:hylton@conacher.co.za
P.S.: I am subscribed to the LO-Users mailing list


-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

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.