Searching multiple fields using MATCH (with OFFSET) and INDEX functions

%3CLINGO-SUB%20id%3D%22lingo-sub-3012477%22%20slang%3D%22en-US%22%3ESearching%20multiple%20fields%20using%20MATCH%20(with%20OFFSET)%20and%20INDEX%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3012477%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20building%20a%20spreadsheet%20that%20catalogs%20books%20for%20the%20teachers%20in%20my%20school.%26nbsp%3B%20To%20make%20it%20easier%20for%20teachers%20to%20search%20by%20Book%20Level%2C%20Genre%2C%20Text%20Structure%2C%20or%20Text%20Feature%2C%20I%20made%20a%20second%20sheet%20in%20the%20workbook%20that%20allows%20searching%20by%20each%20of%20those%20fields%20using%20a%20MATCH%20(with%20OFFSET)%20and%20INDEX%20functions.%26nbsp%3B%20I%20found%20a%20tutorial%20on%20how%20to%20set%20this%20up%20on%20YouTube.%26nbsp%3B%20It%20is%20almost%20working%20exactly%20as%20I%20want%2C%20except%20for%20a%20couple%20of%20glitches%3A%3C%2FP%3E%3CP%3E1)%26nbsp%3B%20When%20I%20search%20for%20%22Fable%22%20under%20%22Genre%22%2C%20it%20doesn't%20return%20any%20titles%20despite%20having%20two%20that%20are%20listed%20as%20%22Fable%22.%3C%2FP%3E%3CP%3E2)%26nbsp%3B%20In%20column%20H%2C%20I%20want%20it%20to%20return%20results%20if%20the%20book%20contains%20any%20of%20the%20text%20features.%26nbsp%3B%20As%20it%20is%2C%20it%20only%20returns%20exact%20matches%20and%20does%20not%20return%20titles%20that%20include%20the%20feature%20I%20am%20searching%20for%20plus%20another%20feature.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20link%20to%20the%20spreadsheet%20as%20I'm%20having%20difficulty%20uploading%20the%20file%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fboonekyschools-my.sharepoint.com%2F%3Ax%3A%2Fg%2Fpersonal%2Fryan_burch_boone_kyschools_us%2FEQJi-reCP71FmlH2Zy2ALNoBOybq7vVgA6CsvtDw2mTG4A%3Fe%3DqLXM7U%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fboonekyschools-my.sharepoint.com%2F%3Ax%3A%2Fg%2Fpersonal%2Fryan_burch_boone_kyschools_us%2FEQJi-reCP71FmlH2Zy2ALNoBOybq7vVgA6CsvtDw2mTG4A%3Fe%3DqLXM7U%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3012477%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3012508%22%20slang%3D%22en-US%22%3ERe%3A%20Searching%20multiple%20fields%20using%20MATCH%20(with%20OFFSET)%20and%20INDEX%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3012508%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1231816%22%20target%3D%22_blank%22%3E%40rburch1979%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EC96%20and%20C100%20contain%20%22Fable%20%22%20(with%20a%20space%20after%20the%20word).%3C%2FP%3E%0A%3CP%3EIf%20you%20remove%20that%20space%2C%20it'll%20work.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20change%20the%20formulas%20in%20C5%20and%20D5%20to%3C%2FP%3E%0A%3CP%3E%3DIFERROR(MATCH(%24D%242%2COFFSET('Browse%20Books'!%24C%241%2CC4%2C%2C1000)%2C0)%2BC4%2C%22%22)%3C%2FP%3E%0A%3CP%3Eand%3C%2FP%3E%0A%3CP%3E%3DIFERROR(INDEX('Browse%20Books'!%24A%241%3A%24A%241102%2CC5)%2C%22%22)%3C%2FP%3E%0A%3CP%3Eand%20fill%20down.%3C%2FP%3E%0A%3CP%3ESimilar%20for%20the%20other%20columns.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0940.png%22%20style%3D%22width%3A%20250px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F329892i3D28D759BE6F8E6C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0940.png%22%20alt%3D%22S0940.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am building a spreadsheet that catalogs books for the teachers in my school.  To make it easier for teachers to search by Book Level, Genre, Text Structure, or Text Feature, I made a second sheet in the workbook that allows searching by each of those fields using a MATCH (with OFFSET) and INDEX functions.  I found a tutorial on how to set this up on YouTube.  It is almost working exactly as I want, except for a couple of glitches:

1)  When I search for "Fable" under "Genre", it doesn't return any titles despite having two that are listed as "Fable".

2)  In column H, I want it to return results if the book contains any of the text features.  As it is, it only returns exact matches and does not return titles that include the feature I am searching for plus another feature.  

Here is a link to the spreadsheet as I'm having difficulty uploading the file:

https://boonekyschools-my.sharepoint.com/:x:/g/personal/ryan_burch_boone_kyschools_us/EQJi-reCP71Fml...

 

 

3 Replies

@rburch1979 

C96 and C100 contain "Fable " (with a space after the word).

If you remove that space, it'll work.

 

I'd change the formulas in C5 and D5 to

=IFERROR(MATCH($D$2,OFFSET('Browse Books'!$C$1,C4,,1000),0)+C4,"")

and

=IFERROR(INDEX('Browse Books'!$A$1:$A$1102,C5),"")

and fill down.

Similar for the other columns.

S0940.png

@Hans Vogelaar 

Thanks so much.  I used your IFERROR suggestion and it cleaned things up greatly.  

 

How can I change the formulas in columns G and H to search for "contains" a text feature in column H instead of "exact match"?

 

For example, if I want a list of all books containing "Speech Bubbles" and not just the books that ONLY contain Speech Bubbles.  Thanks again for your help.  Much appreciated.  

 

@Hans Vogelaar 

Disregard.  I got it to work using wildcards.  Thanks again.