making a book list

[kosisme1]

HI, fairly new to excel and I am trying to make a book list containing book title, shelf location, author, and perhaps date published. It is for a bookcase with eight shelves and approx sixty books per shelf, the bookcase is quite full and I can't arrange them in order physically as they are of different sizes and that would use up too much space. Ideally the list would include some easy way of finding a shelf location  from say book title or author, if a search box is only possible by writing code then this would be beyond me.

Have tried making a short sample list and the best I can figure out is to make the first column book title, then the adjacent column shelf location, then author etc, which means I can custom sort book title A-Z, which would make it easier to find, but what if I can only remember say authors name, how do I find a shelf location from this.

[#2]

@kosisme1 

Take a look at the attached example. It uses the FILTER function (available in Excel in Microsoft 365 and Office 2021) to allow you to search by title, author of shelf.

[#3]

@kosisme1 

Here is another version which uses VBA to search the book's database on a sheet called "Search".

Replace the book database on the "Data" sheet with the actual data and then you may search the database on the sheet called "Search".

[#4]

@kosisme1 

This is a variant on @Hans Vogelaar 's solution.

BookSearchλ([title], [author], [shelf])

= LET(
    isTitle,   ISBLANK(title)  + ISNUMBER(SEARCH(title,BookList[Title])),
    isAuthor,  ISBLANK(author) + ISNUMBER(SEARCH(author,BookList[Author])),
    isOnShelf, ISBLANK(shelf)  + (BookList[Shelf]=shelf),
    FILTER(BookList, isTitle*isAuthor*isOnShelf)
  )

[#5]

@kosisme1 

An alternative could be Power Query. In the attached file you can enter all books, authors, shelf locations and years in the large blue dynamic table. Then you can enter the search criteria in the small blue dynamic tables. You can search for one or more books, authors or shelf locations at the same time. After entering the search criteria you can click in any cell of the green table, right-click with the mouse and select refresh.

[#6]

Thanks for the rely, power query sounds ideal, is there any "how to" for this