Excel query

  Marko797 10:04 24 Dec 2007
Locked

I've been asked to produce an Excel spreadsheet document which lists a company's suppliers, which is to be searchable by supplier name, and also by what services/products they provide.

They have many providers so the spreadsheet will be quite big, and this is what needs to be managed.

Is this just a case of using the 'find' facility in the normal menu, or is there a fancy formula which would be better and quicker?

The company concerned are using MS Office 2003 version. Thnx in advance.

  pj123 13:08 24 Dec 2007

I think you will find that Excel wouldn't be much good and you would be better off using a Database and not a Spreadsheet.

MS Access would be my choice but unfortunately it is far too complicated for me so I use MS Works which is a basic Flat File Database but it does have reasonably good search facilities.

  VoG II 16:07 24 Dec 2007

I agree that a relational database like Access would be preferable to Excel.

However, Excel does have lookup functions so you could create a lookup table. If your table is arranged like this using columns A and B

Supplier Service
S1 X
S2 Y
S3 Z

them with a supplier name in say N1 you could easily look up the service provided using the formula

=VLOOKUP(N1,A$2:B$1000,2,FALSE)

However, you can only lookup columns to the right of the search term. With the above example you would need to use a combination of INDEX and MATCH to return a supplier associated with a service. You can find examples of that if you click here

With both methods, the lookup will return only a single result, so if several suppliers provide a given service the native Excel functions will return only a single match. There is a multiple lookup function available on this site click here - change the language to English (unless you are Dutch of course).

  Marko797 18:36 26 Dec 2007

pleased to see VoG (the excel guru) on the case. Thnx for the links too.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?