0

I have a weird LibreOffice Calc problem. I have a table of chapters from the five published books in the A Song of Ice and fire series. Here is a facsimile:

      A                      B
1   | Book                 | Chapter
----+----------------------+-------
2   | A Game of Thrones    | 0
3   | A Game of Thrones    | 1
...
75  | A Game of Thrones    | 73
76  | A Clash of Kings     | 0
77  | A Clash of Kings     | 1
...
147 | A Storm of Swords    | 0
...
230 | A Feast for Crows    | 0
...
276 | A Feast for Crows    | 46
277 | A Dance with Dragons | 0
...
350 | A Dance with Dragons | 73

I'm using a MATCH and OFFSET formula to find the last chapter in every book, by first MATCHing the last row for a value in the Book column, then getting the value from the Chapter column. So, to find the last chapter in A Game of Thrones:

=OFFSET("A Game of Thrones", MATCH(A4, A2:A350, 1), 0)

Then this would output 73.

This works for the first three books. A Feast for Crows and A Dance with Dragons don't work. When I run the MATCH formula:

=MATCH("A Feast for Crows", A2:A350, 1)

It should return 276, which is the number of the last row with the value "A Feast for Crows", but it returns #N/A. However, when I use the range A75:A350, it returns 72. If I use A76:A350, it returns 71, and so on, with the output decreasing as the lower limit increases. Until I change the range to A110:A350, where the value becomes 167 (again decreasing, inversely proportional to the lower limit of the range.) The search criterion is correct, there are no leading or trailing spaces or miscapitalised words. I'm using LibreOffice Calc 4.3.1.2.

The results of the MATCH are baffling me; I don't know why it's not outputting correctly. Can you suggest what's gone wrong here?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Lou
  • 2,200
  • 2
  • 33
  • 66

2 Answers2

0

Have you tried =MATCH("A Feast for Crows", A2:A350, 0)? The 1 will produce close matches, the 0 produces exact matches.

DTS
  • 423
  • 2
  • 13
  • I thought using the 1 parameter at the end searched the list in descending order. I need this functionality in order to find the last instance of, for example, "A Feast for Crows" in that column. That way I figure out what number the last chapter is. – Lou Sep 11 '14 at 11:23
  • `MATCH(lookup_value,lookup_array,match_type)` – DTS Sep 11 '14 at 11:25
  • And yes, I have tried this. It gives me the row above the *first* instance of that value. So if I was searching for A Feast for Crows, it would give me the row number for the last value in A Storm of Swords. – Lou Sep 11 '14 at 11:25
  • That's because you're starting your array in A2, use the array `$A:$A` to search the entire column. – DTS Sep 11 '14 at 11:27
  • Even if I use A1:A350 ($A:$A didn't work,) it doesn't solve my problem; I'm trying to find the last instance of the value, not the first. When I use 1 as a parameter, it only generates correct results for A Game of Thrones, A Clash of Kings and A Storm of Swords; A Feast for Crows and A Dance with Dragons both generate #N/A. – Lou Sep 11 '14 at 11:29
  • 1
    Sorry, thought from your tags you were using Excel, not LibreOffice (re-read your question). I'm as stumped as you are thenn :/ – DTS Sep 11 '14 at 11:33
  • Yeah, ordering tags by popularity is annoying. Thanks for trying to help. – Lou Sep 11 '14 at 11:35
0

You can't use MATCH to achieve your requirements. MATCH can only match the first occurrence of the searchitem with exact match with matchtype 0 or, with matchtype 1, the position of the largest value in searchregion that is less than or equal to searchitem. But therefore searchregion is assumed to be sorted in ascending order. This is not the case.

See: https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_MATCH_function.

You could use the following array formula in C1:

{=INDEX($B$1:$B$1000;MAX(IF($A$1:$A$1000=A2;ROW($A$1:$A$1000))))}

Enter the formula without curly brackets in C1 and press [Enter]. The result should be 0. Now open the function wizard (fx) and select the [x] Array checkbox there, then click [OK]. Now the result should be 73 with your data.

Now Copy and Paste C1 to C2:Cn. Do Copy and Paste. Do not fill with the fill handle because then you expand the array formula as it is and A2 will not change to A3, A4...

The formula in C1 does the following: Get the MAX of the ROW numbers where A1:A1000 = A2 ("A Game of Thrones") and INDEX this ROW number in B1:B1000. So it gets 73 from B75.

Greetings

Axel

Axel Richter
  • 56,077
  • 6
  • 60
  • 87