Re: [libreoffice-users] vlookup help

So, using your spreadsheet example, here is what I came up with:

The formula figures out where the values are in column "A" and the
uses "X" to offset the value to return the word.

This is how it looks:

 Row | Col A | Col B | Col C
 n   | One   | Num X | {Magic formula}
 n+1 | Two   |       |
 n+2 | Three |       |
 n+3 | Four  |       |
 n+4 | Five  |       |

This is {Magic formula}
  =INDIRECT(ADDRESS(ROW()-ROW($A$n)+$B$n+ROW(),COLUMN($A$n:$A$n),4,1),1)
  (The actual row number "n" is put in the formula in four places.)

And this is what you get.
 Row | Col A | Col B | Col C
 n   | One   |   4   | Four
 n+1 | Two   |       |
 n+2 | Three |       |
 n+3 | Four  |       |
 n+4 | Five  |       |

Broken down it says: (Starting from the inside)
  ROW()-ROW($A$n)+$B$n+ROW()
    Return the value of what row this cell is in (i.e. Col C, Row n)
    then subtract the row where we're starting from (top row) Add in
    the offset number you provide (Col B, Row n) and then add in the
    row where we're working from (last 'row()' call.)

  COLUMN($A$n:$A$n)
    Then return a value for the columns we're working in (where the
    letters are, "A")

  ,4,1)
    (4 Means return the value as A8 instead of $A$8 and the 1 means
    say it as A8 instead of R8CA)

  ADDRESS( row, column, 4, 1 )
    Now, treat all that as an address of a row and column.

  INDIRECT( address from above, 1 )
    Go get the *VALUE* at that address and display it




On Thu, Jul 11, 2024 at 12:39 PM Michael Tiernan <michael.tiernan@gmail.com>
wrote:

> I'd posted a piece about using indirect references on the Libreoffice
> forum.
> This is where it is:
>
> https://ask.libreoffice.org/t/how-can-i-get-the-current-cell-column-letter/39139/4?u=her_kitty_daddy
>
> The thing was how to get the values another cell points to.
>
> If you used that sort of solution with the column fixed as "A" and use the
> value in "C" to refernce the row involved.
>
> See if that leads you to a solution?
> --
>     << MCT >>   Michael C Tiernan.    http://www.linkedin.com/in/mtiernan
>
>     Non Impediti Ratione Cogatationis
>     Women and cats will do as they please, and
>     men and dogs should relax and get used to the idea. -Robert A. Heinlein


-- 
    << MCT >>   Michael C Tiernan.    http://www.linkedin.com/in/mtiernan

    Non Impediti Ratione Cogatationis
    Women and cats will do as they please, and
    men and dogs should relax and get used to the idea. -Robert A. Heinlein

-- 
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