0

I have a table of chapters from the book A Game of Thrones, and the page that this chapter starts. I want to take a given page number, find the nearest numerical value going down, and then pick out the chapter name for that row. For example:

Eddard II   -  104
Tyrion II   -  113
Catelyn III -  122

Given the input 119, I want to find the next lowest number on the right-hand column, which is 113, and then output the chapter name on the left-hand column, so Tyrion II. I can't work out how to do this formulaically: it looks like it should be a job for VLOOKUP, but I don't know if VLOOKUP can accommodate ranges, less than or greater than when looking for a row. How should I go about this?

Excel-based solutions are also fine, so long as they're applicable to Libreoffice Calc.

Lou
  • 2,200
  • 2
  • 33
  • 66

1 Answers1

1

Assuming the chapter names are in column A and the page numbers are in column B and the given input is in C1, then 

=INDEX(A1:A10,MATCH(C1,B1:B10,1))

VLOOKUP can also search the nearest value in sorted columns - see help VLOOKUP and there the parameter "sort order". But it cannot look to the left, so you have to use INDEX MATCH in this case.

Greetings

Axel

Axel Richter
  • 56,077
  • 6
  • 60
  • 87
  • That's great, works perfectly. Would you be willing to explain why it works? I'm not familiar with MATCH. – Lou Aug 29 '14 at 09:59
  • There is a help page for each spreadsheet function in libreoffice. Please use it. https://help.libreoffice.org/Calc/Spreadsheet_Functions#MATCH – Axel Richter Aug 29 '14 at 10:06