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.

RockPaperLz- Mask it or Casket
  • 7,741
  • 30
  • 73
  • 122

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
  • 27,064
  • 61
  • 75
  • 116
  • @RockPaperLizard: The MAX is what gets you the last (maximum) qualifying row. – fixer1234 Oct 15 '15 at 04:45
  • The ctrl+shift+enter trick works, although I have hundreds of rows, so that could be an issue! It outputs "1" for each cell, so the value is incorrect, but at least the error is gone! – RockPaperLz- Mask it or Casket Oct 15 '15 at 05:06
  • Son of a gun, it does seem to work! But to manually enter it in for hundreds of rows, remembering to press ctrl+shift+enter each time... ugh... and then if I want to add more rows, I have to redo everything! The good (or bad) news is there does not appear to be some simple way to do this that I was simply overlooking. Unless, of course, there is, and we're both without that knowledge! :-) – RockPaperLz- Mask it or Casket Oct 15 '15 at 05:19
  • @RockPaperLizard: Looks like you're in luck. I went in and changed the lookup ranges to rows 1 through 50 (four ranges in the formula). Everything after row 6 in my test example is empty space. Saved it with ctrl-shift-enter. Then copied and pasted to the rest of the range (rows 2-6). It worked. Which also means that you could just copy and paste additional formulas when you add data. Or build in a test for blank, prepopulate a large range, and hide the results for the empty rows. – fixer1234 Oct 15 '15 at 05:35
  • LOL... About 10-15 minutes ago, I decided "why use a scalpel when an axe will do". I created a bunch of hidden columns and tabulated everything in them. Hmmm... but now you say that the array formula works for you... time to decide which to use! – RockPaperLz- Mask it or Casket Oct 15 '15 at 05:41
  • To have the MAX reflect the most recent, the range needs to be changed for the formula in each row. Otherwise, it will always find the largest value. I have yet to determine if adding/deleting rows will mess it all up. – RockPaperLz- Mask it or Casket Oct 15 '15 at 05:45
  • @RockPaperLizard: Nope, it's the easy case. The formula contains four (matching) row ranges. Make each one rows 1 to 1,000 or 10,000 (pick a number that's way more than you'll ever need). In C1 set the lookup target to A1 (2 locations). Save with crtl-shift-enter. Then copy as needed (ctrl-C to copy, ctrl-V to paste in a block of cells), repeat the copy/paste whenever you need more. The MAX works on just the rows that match the target (unused rows won't match so they don't make any difference). I tested it, you're good. – fixer1234 Oct 15 '15 at 05:58
  • Thanks! I will continue to play with it! I am weighing the costs/benefits of the hidden columns technique vs the powerful formula. It's surprising that this functionality is not included! – RockPaperLz- Mask it or Casket Oct 15 '15 at 06:10