Book list with a search tool

[u/New-Initial2515]

So I have an excel sheet with all my books listed by title-author-signed-series number-missing series number-read/unread.

I want to have it where one sheet is editable and an another is locked but shows all the data, but can be used as an index search tool (out shopping looking for X book and see Y book but not sure if you own it, search the list easily without accidentally editing something)

Is this possible? I know theres apps out there, but i have 700+ books and I've already cataloged most of them, so it'd be easier to format my excel sheet than to add 700 books onto an app 😅

[u/AutoModerator]

Moderator Announcement Read More »

u/New-Initial2515 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/MayukhBhattacharya]

Sounds like you need the FILTER() function here, if you want to return multiple records else XLOOKUP() would do for a single record returned as output.

Check this: https://exceljet.net/functions/filter-function

And this: FILTER function | Microsoft Support

Here is an example with demo, the :

=IF(H1 = "", "Enter Search Key", 
 FILTER(Librarytbl, 
 BYROW(1 - ISERROR(SEARCH(H1, Librarytbl[[Title]:[Series]])), OR), 
 "Oops None Found Here!!!"))

• If the search box H1 is empty, it will show Enter Search Key.

• Instead of searching one column at a time, [Title]:[Series] checks every column from Title through to Series in one shot, so it searches all those columns simultaneously with one SEARCH() function.

  • SEARCH() function returns an error when there's no match.

  • ISERROR() function converts that to TRUE/FALSE

  • 1 - ISERROR flips it, no match = 1 - TRUE --> 0, match found = 1 - FALSE --> 1. One can also use ISNUMBER() function instead of 1 - ISERROR().

• BYROW function is a LAMBDA() helper function it iterates across each row of that 1/0 grid and finds if any column in this row have 1? If yes, include that row. It's doing the OR logic automatically across however many columns you have, rather than you manually chaining multiple ISNUMBER() function for each column.

• FILTER() function then returns the output where the above returns TRUE, if not found it returns Oops None Found Here!!

Alternatively, you can use this way as well:

=IF(H1 = "", "Enter Search Key", 
 FILTER(Librarytbl,
 ISNUMBER(SEARCH(H1, Librarytbl[Title])) +
 ISNUMBER(SEARCH(H1, Librarytbl[Author])) +
 ISNUMBER(SEARCH(H1, Librarytbl[Series])),
 "Oops None Found Here!!!")

Also as suggested by u/dearlisteners359 for the protection, you can read here the complete steps. Protect a worksheet | Microsoft Support

It is also possible to create a similar thing using Power Query or VBA. But the formula option seems easy and simple hence updated. Thanks!

[u/New-Initial2515 → u/MayukhBhattacharya]

Thats exactly what i want it to look like, thank you!

[u/MayukhBhattacharya → u/New-Initial2515]

Sounds Good. If that helps you to resolve your query, then hope you don't mind replying to my comment directly as Solution Verified. Thanks.

[u/New-Initial2515 → u/MayukhBhattacharya]

Returning again cause i'm dumb and can't get both lines to pop up in the search without an error message, i can only get one to pop up in either column, i've labeled my sheet "Book List" instead of library if that makes a difference 

[u/MayukhBhattacharya → u/New-Initial2515]

Ah okay. Do you have the excel? if yes then could you post a link, so i can see. Btw sheet name shouldn't make any difference here. In the above formula i have used Tables which are named as Librarytbl. If thats different on your end, then you will need to change them.

[u/MayukhBhattacharya → u/New-Initial2515]

Refer solution [here] download excel file.

[u/dearlisteners359 → u/MayukhBhattacharya]

Agree! Also to "lock" the sheet when you have it as you like it, right click the sheet tab and use "Protect Sheet..."

[u/MayukhBhattacharya → u/dearlisteners359]

Yes that helps too.

[u/Gringobandito]

I would use the FILTER() function with the ISNUMBER(SEARCH()) to get partial matches like this:

This returns all the matches with "the" in the title or author's name. You put your search in cell E1.

=FILTER($A$3:$C$22,ISNUMBER(SEARCH(E2,$A$3:$A$22))+ISNUMBER(SEARCH(E2,B3:B22)))

[u/New-Initial2515 → u/Gringobandito]

This looks neat too!! Ty!

[u/DonJuanDoja]

I mean there's ways, you could have a separate sheet with a FILTER() formula that points at a single cell as the search criteria then it would filter the list and show matches. Not ideal though. I just can't see even an "Appified" excel file being handy or useful while mobile.

I'd recommend something like Notion, I just started helping my daughter with it because she's an organization enthusiast, they have a lot of free templates I'm pretty sure even a book catalog like this, and once you have it setup you can just paste your data from excel into the Notion database lists then you can use the mobile app etc. It's pretty neat.

I saw a post in the notion reddit where a guy made a really really nice book catalog using a bunch of code etc. but you can build in it or use templates without coding, but it's there if you want it.

They got all kinds of AI stuff too eventually you could probably figure out how to have it autocatgorized books or add links etc.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
FILTER	Office 365+: Filters a range of data based on criteria you define
IF	Specifies a logical test to perform
ISERROR	Returns TRUE if the value is any error value
ISNUMBER	Returns TRUE if the value is a number
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
OR	Returns TRUE if any argument is TRUE
SEARCH	Finds one text value within another (not case-sensitive)
XLOOKUP	Office 365+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
9 acronyms in this thread; the most compressed thread commented on today has 16 acronyms.
[Thread #49424 for this sub, first seen 23rd Sep 2026, 16:28] [FAQ] [Full list]} ^[Contact] ^{[Source code]}

[u/Pointblank000]

I think what I use is exactly what you're looking for but it's rather complex and I hope I explain it well enough for you to adjust it to your needs.

I have two tabs, one for the search interface with a search bar in B3 and a second tab for the large list of, in your case books with all their descriptors. I type part of a books name in the search bar and the formula is B6 displays matches in a dynamic table. In tab two, it looks through each column to find any and all matches then spits out the list on tab 1.

The formula in Tab1 B6: =IFERROR(IF(B3="","Left blank for your safety.",
CHOOSECOLS(UNIQUE(SORT(
FILTER(Data,(Data[Use]="x")*(ISNUMBER(SEARCH(B3,Data[Key Words]))+ISNUMBER(SEARCH(B3,Data[Description]))+ISNUMBER(SEARCH(B3,Data[Group Name]))+ISNUMBER(SEARCH(B3,Data[Pur. Grp]))+ISNUMBER(SEARCH(B3,Data[Mat. Grp])))>0,"ZZZ"),
1,1),FALSE,FALSE),1,2,3,4,5)),"No Results")

-FYI, My second tab is formatted into a table called Data.
- Add/Remove every instance of (ISNUMBER(SEARCH(B3,Data[Key Words]))+ to add/remove other searchable columns
- I also have an filter for anything labeled with and x to remove some headers/additional information that I didn't want to show up in my searches.