I have started to create excel search index (like in a library) but have over thought it. Im trying to get back on track here. I am thinking that a "match, index" function might work but I may not have it setup right?
Problem:

1) I have a Library index that is just messed up(things are organized by multiple persons with multiple agendas)

2) Want to just enter a "name" of item and it will give me a location in library to go and find it.

3) Need to create a digital search of the index, but what are my choices, vlookup or tables or something easier.

I have attached my progress



Looking for fresh ideas to start over.
any ideas are welcome, don't be afraid to say some think.

Attached Files

rev. 3.xlsx (45.4 KB, 10 views)

Hi Jaime,


Re 2, I can't see where or even on which worksheet you want to enter the search name or how you propose/want to relate Columns A & B in the 'Index' worksheet to their apparent equivalents, columns H:K in the 'search' worksheet.


As for the data, it seems you have at least four IDs that can be used by different parts of the organisation to identify the same items - the DRAW. TAG, Plant Nomenclature, O&M TAG and the FAMIS NUMBER. Some of these seems to be duplicated, but the dupicates don't necessarily refer to the same items (eg DRAW. TAG 'CHP-01' refers an item for which the O&M NAME gives two descriptions - CHILL WATER PUMP -1, BALDOR AC/TEFC MOTORS & SUCTION PUMP - the second having no O&M TAG and the last having a completely different O&M TAG than the first). That's certainly an inefficient arrangement, is liable to cause confusion at times and could ultimately end up being costly. And how one is supposed to create a definitve index with such inconsistencies in the data is beyond me.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Paul

Thank you and you are right-on here. I have not specified the main goal of the search.
I have clean it up and added criteria. You will see the search page and what Im after. I think a "index, match" function is best? But the area of search labeled 2a,2b,2c will required a different kind of search, I think, and may require more thought (have sub-in drop down as example) but this is the direction I am trying to achieve.
Please see rev.3 , at first thread.
I see another problem of have more then one answer here, no clear to handle that either, drop down? or just multiple list?

OK, while one could use formulae to retrieve all the matches for a given set of search criterion (and they're fairly complex formulae, at that), you really would need to have a separate results pane for each four sets of criteria; otherwise it'd all end up being one horrible mess.

Better still, IMHO, would be to not bother with the Search worksheet and just use filtering on the 'O&M plus' sheet. See attached.

Now, suppose you want to find all the records for a particular item (or even more than one item) in any of the columns. Select that column's dropdown filter, uncheck 'All', then scroll down to the item(s) you want an check it/them. If you only want to find the subset of those items that correspond to entries in another column, do the same there. Repeat as needed. When you're finished, simply re-check the 'select all' option for all the filtered columns.

A variation on this theme is to filter out one or more items, by unchecking only those items (ie "I'm interested in all except for ...").

The filtering approach is far more flexible than what you're proposing, doesn't require one to know the terms in use before search can be started, requires no formulae, and is not so susceptible to spelling errors (you have 'Calibration mis-spelt in column G).

Attached Files

Rev 3A.xlsx (46.2 KB, 12 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Paul
Alright, mmm filtering only. I will mess around with this and for now this may work. These O&M volumes are messed up, especially the indexes and I have 50 volumes to go through, to straighten out. I wanted to create a quick index search as a temporary as well. I was crunching through some indirect function to see how it looked but was heading into bush real fast(see attachment) and now have Family in hospital last night so lest me digest this and get back.
Super Thank you
Hope your summer is good
WCA

Attached Files

rev. 4.xlsx (49.3 KB, 10 views)