1

I have created a LibreOffice Calc spreadsheet with columns A-C. In column A, I have a list of parts purchased (in chronologically ascending order). In column B, I have a list of corresponding prices. In column C, I want to show the last purchased price for the current part.

This is what I've come up with to do it:

=INDIRECT(CONCATENATE("B",TEXT(MATCH(A8,A1:A7,1),"#")))

It basically works, but there are two issues:

  1. It is very convoluted. Is there a simpler way to accomplish this?
  2. If the part has never been purchased before, it will show the price of whatever part is on the row above.

UPDATE: With more testing, it definitely does not work as expected. I can't tell if it is a bug in the MATCH function or if it is not the appropriate function for the job. The MATCH function finds "smaller or equal", and I think the "smaller" condition is causing problems.

Amazon Dies In Darkness
  • 9,990

1 Answers1

1

This is a pretty similar problem to How to get VLOOKUP to return the last match?. A solution by Excellll pretty much fits your requirement.

=INDEX(IF($A$1:$A$1000=A1,$B$1:$B$1000),MAX(IF($A$1:$A$1000=A1,ROW($A$1:$A$1000))))

I made the lookup ranges arbitrarily large. You can use a huge range so you don't need to adjust the formula as you add more data. My test example had just six entries; the unused rows won't match anything so they are ignored.

This is the formula that goes in C1, so the lookup target is A1 (two locations).

The MAX function finds the highest number matching row (chronological order, so that will be the most recent).

It's an array formula, so it needs to be entered with Ctrl-Shift-Enter. Once C1 is entered you can copy the cell with Ctrl-C, select a range of cells, as needed, and paste with Ctrl-V. If you add more data, copy and paste another block of cells.

Or, wrap this in a blank-cell test and pre-populate more cells than you'll ever need:

=IF(ISBLANK(A1),"",formula_above)

With this approach, the prepopulated cells will remain blank until you add data for the row.

Community
  • 1
fixer1234
  • 28,064