-1

I have a calc spreedsheet, which is mostly similar to excel regarding formulas. In one sheet I have the details of some boughts I've done (comprasDet) and in another the items I can buy (productos). The first entry in comprasDet looks like this:

comprasDet sheet

I need to retreive from productos the last registered cost from "Shampo y Mascarilla Chocolate....", put it in ctUnit (that stands for unitary cost) and then calculate the entry cost in ctTot. The last registered costs in the productos sheet are:

productos sheet In this case, I need the value in the F12 cell - $6.00. Now, I know that with:

MATCH(D2,$productos.D:D,0) (written in comprasDet)

I can get the row number of "Shampo y Mascarilla Chocolate..." in productos (12), and with (formula taken from this this post):

LOOKUP(2,1/($productos.12:12<>""),$productos.12:12)

I get actually the last value in the 12th row ($6.00) - the thing is, I need to be able to look for the last price for any given value in comprasDet, for example, if instead of "Shampo y Mascarilla Chocolate..." I have "Keratina Brasileña (1 lt)" I'll would like to get the F4 value in productos ($7.00).
The sheet productos will be getting more columns as the prices vary, for example, in the previos print, there were only two price columns (ct20210918 and ct20211001), here is an example with another price modification:

productos sheet

And, in this scenario, the price I need to get from "Shampo y Mascarilla Chocolate..." with a formula inside the comprasDet sheet will be the value in productos in cell G12, $8.00.

Note: I know that in excel the notation to reference cell/s in another sheet is Sheet_name!First_cell:Last_cell, in libreoffice calc changes a little, Sheet_name.First_cell:Last_cell, but the formulas are pretty much the same.
Thanks in advance.

Gerardo
  • 81
  • 7
  • **DO NOT post images of code, data, error messages, etc.** - copy or type the text into the question. [ask] – Rob Sep 22 '21 at 01:42

1 Answers1

0

Edit: With the new requirements, you need to use COLUMN and MAX to find the last used column. Because you are looking for the last used column of a particular row, you need to use INDIRECT to reference only that row. Here is how you do it in excel. You will have to modify it for your program

=INDEX(productos!A:AD,MATCH(D2,productos!D:D,0),MAX(COLUMN(INDIRECT("R"&MATCH(D2,productos!D:D,0)&"C1:"&"R"&MATCH(D2,productos!D:D,0)&"C30"))))

Explanation: MATCH(D2,productos!D:D,0) gets the correct row in the productos table. I'll call it MyRow.

INDIRECT("R"&MyRow&"C1:"&"R"&MyRow&"C30") creates a reference to the first 30 columns of MyRow. I'll call it MyRange. Note, "30" is an arbitrary number. Make it large enough that you always get the data.

COLUMN(MyRange) creates an array of column indexes for all filled cells in MyRange.

MAX(COLUMN(MyRange)) gets the largest column index, which is the last filled cell. I'll call this MyCol.

`=INDEX(productos!A:AD,MyRow,MyCol)' returns the last used column of the row you are interested in.

Original Answer: If this was excel you would use XLOOKUP.

=XLOOKUP(D2,'productos'!D:D,'productos'!F:F,"-",0,-1)

If your program has access to the index function, this should work:

=INDEX($productos.F:F, MATCH(D2,$productos.D:D,0))
Dave Thunes
  • 260
  • 2
  • 9
  • Thanks @Dave, CALC don't use XLOOKUP, and the main problem is that I need to write the formula in one sheet to look for the value in another, and the number of columns to search in the 2nd will be increasing - I modified the question in order to explain this. – Gerardo Sep 22 '21 at 00:07
  • @Gerardo, See my edit. Hope this helps. – Dave Thunes Sep 22 '21 at 13:56