Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using an excel sheet to automate the process of calculating fiber dataloss.

The problem I have is the following:

I am using the values in the "Soorten Vezels" part of this sheet to generate a dropdown menu in another part of the worksheet. To keep this excel file really customizable, I want to be able to add a new type of fibre and then just add the max allowed loss next to it.

Soorten Vezels

Somewhere else in this sheet, I will be able to choose the type of fibre in a dropdown menu and then the excel sheet will automatically enter the max loss ( the one next to it in the pre-made list) in another field next to it.

I hope I have made this clear enough


By using this:

=OPZOEKEN([Type kabel];Tabel3[#alles])

with "type kabel" will be the value wich I am looking for in tabel3 and this works. However, if I do the same in another field:

=OPZOEKEN(Tabel1[Type Connector];Tabel5[#alles])

it gives me the error:


What Is wrong?

share|improve this question
I don't have Dutch to try this, but if your lookup array is Tabel1 and the value to be looked up is in F1 does =OPZOEKEN(F1;Tabel1;2;0) work? – pnuts Jun 10 '13 at 18:10
It does seem to work so far – Gutanoth Jun 11 '13 at 5:38
up vote 0 down vote accepted

You can use a Vlookup function for that.


... where A1 is the cell with the drop-down value. Depending on your layout, you could build a dynamic range name for the data validation list and the lookup table, so you don't have to change your formula every time you add a new "Soorten Veezels". For the data validation list use the named range LookupList with a formula like


For the lookup table use the name LookupTable with the formula


Then you can use =LookupList as the reference for the drop-down and use the vlookup like this

share|improve this answer
I get the following error: #N/B – Gutanoth Jun 10 '13 at 12:42
@Gutanoth Apart from the function name, the usual 'catch' when using languages other than English is the separators (seems you need ; where the formulae above apply ,). – pnuts Jun 11 '13 at 5:41

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .