Index Match

[Vertual Lord]

Hey,

I'm trying to make a search function in my music database in excel 2003.
Figured Vlookup won't work since that only looks in the first column.

See attachment.

All songs are in tabs ABC, DEF, ...
Sheet "Alles" gathers all songs from all the other sheets.
The reason I don't just use one sheet is that I don't want to have to scroll down to each letter to see the songs that are in there, I don't want to have a database with like 1500 rows (since I will be adding a lot more songs).


What I'd want to have is a search option in the "Zoeken" sheet.
Say I type "Always look on the bright side of life", it should look that up and show: "Monty Python" "(album)" "(year)"

However if I want to search for a performer, example Billy Talent, it should show me all of their songs, each with the album name and year. (about 50 songs).


Any help for this would be great,
thanks!

[6StringJazzer]

I don't want to have a database with like 1500 rows (since I will be adding a lot more songs).

But that is exactly what you should have. What you are describing is a classic database problem. If you want to look just for an exact match on song title, like your example, it's still pretty easy to do in Excel, and you would use the Alles sheet for that search, but you need to reorganize the data. There is no benefit in having a separate set of columns in Alles for each letter of the alphabet. That makes it much more difficult to do a lookup on that sheet; you have to use a nested formula.

I do not understand why you are merging cells in every sheet; merging cells can cause problems in formula references and I don't see any advantage to it in this case.

I have shown how this could be done, rearranging the ABC data on Alles as an example. But going to a single database would give much more powerful search and sort capabilities.

However if I want to search for a performer, example Billy Talent, it should show me all of their songs, each with the album name and year. (about 50 songs).

This is more difficult and may require VBA.

[JBeaucaire]

Never never never create database for your eye, just don't do it. There is no better starting advice I can give you.

I've moved all your songs into a single sheet where they belong. EVERYTHING you want to do from here is now simpler, I promise.

I've added the search functions to your front sheet and put formulas going down 200 rows. You can extend that later if needed.

1) Select SONG or PERFORMER in cell A1 to indicate the type of search you are doing

2) Type a single letter "a" into cell B1, and you're looking at all the songs that were originally your "a" list. See? easy!

3) To update a song, simply click the LINK off to the right and you will be jumped directly to it on your Songs sheet.

However many letters you type, that will be used as the starting search.

You can search for all performers starting with "M" or just "Mon", you get the idea.

4) You do not have to sort your Songs sheet, but you can. Add songs to the bottom and sort if you think it's helpful. The search tools on the first sheet may make that unnecessary.

[ChemistB]

I concur with 6 String. You should have 1 sheet where data is inputted. If you want, you can set up the ABC, DEF...sheets to pull data from that database sheet. Then you could easily pull song, year, artist from the database sheet. As it is now, only the artist name tells Excel what sheet to search on. Searching album, song title, etc would require searching all sheets which would require VBA.

[Vertual Lord]

@Jbeaucaire

That's neat!! Thank you so much, couldn't have imagined it better than that!!!
I'll be trying to decipher the codes you used now, learning is never a bad thing! Couple combinations I never would've thought of, and some formulas I didn't know...

[JBeaucaire]

Glad to help.

[mehmetcik]

Here is another Version.

click on the search Tab at the bottom of excel.

[Vertual Lord]

Glad to help.

Just another question. I've been using this alot now (It has like couple hundres of songs in it now, really something nice you made for me there!!) but have been experiencing this:

For example a song is called "It's a beautiful day" (by Michael Bublé). Say I forget the performer, and I think the song is called "Beautiful day".
When I type in Beautiful day, it will not find it, because the first words (It's a) are missing.

Any easy way to get around this? If not leave it be, if there's an easy way that would be great!

Thank you

[JBeaucaire]

Can you upload the test file for me to review this phenomenon? Thanks.

[JBeaucaire]

Here, try this this.... change the KEY formula in E2 to this....

=IF(Zoeken!$A$1="Song", IF(COUNTIF(A2, "*"&Zoeken!$B$1&"*")=1, N(E1)+1, N(E1)), IF(COUNTIF(B2, Zoeken!$B$1&"*")=1, N(E1)+1, N(E1)))


Unfortunately doing this means the "one letter" entries won't give an alphabetic listing. The whole reason we did the KEY the way we did originally was so you only had to guess the first few letters right.

Another idea is to leave the KEY formula as is and put "variations" on titles into the database. For exemple.

The Wild West
Wild West, The

It's a Beautiful Life
Beautiful Life, It's A


Based on the way you search only one variation would appear. The formula tweak above fixes this problem and creates a much less smart tool.

[JBeaucaire]

Hah, I realized there's no need to choose one or the other, you can have both. I've added a "switch" on the front sheet where you can click to temporarily change the function from showing the starting strings to showing ALL the songs where the string you've typed in B1 is a substring. Just use the checkbox to toggle that.

[Vertual Lord]

Hah, I realized there's no need to choose one or the other, you can have both. I've added a "switch" on the front sheet where you can click to temporarily change the function from showing the starting strings to showing ALL the songs where the string you've typed in B1 is a substring. Just use the checkbox to toggle that.

Amazing!
You sir are the best! :D
Thank you so much