Value lookup when the value is unknown

[sx200n]

Hello, I basically work for an insurance broker and I am attempting to create a commission calculator within excel. I need it to allow the user to simply enter firstly a total amount in one cell. And for them to then enter the initials (two digits only) of the insurance company the premium is with. This is the issue. I cannot for the life of me get a formula to work as the formula will not know the value to check until the user has entered it.

I need the formula to check the two digit insurer initial (against my hidden tab that contains all initials and also the commission levels in a seperate column) and return me just the value of the commission.

Any ideas? I have tried the VLOOKUP formula but it seems to want to know the value to search for, yet the cell won't know this until the user has entered it.

HELP!!!!

[Special-K]

Can you post a dummy spreadsheet with your input and expected results?

[sx200n]

Here is the current file. Yet to be finished. But the input cells and hidden tabs are here.

[Special-K]

in Staff Discount Calc!E9
=IF(COUNTA(E4:E6)=3,VLOOKUP(E5,IF(E6=Lookup!B2:B605,Lookup!A2:D605,""),4,0)*E4,"")

It's an Array formula, use Ctrl-Shift-Enter

[sx200n]

You are a star. That seems to work nicely.

Cheers