my lookup table gets confused?

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

the options for the drop down lists are in the same column down in the 40s rows. C & D are the drop down options and E is the list of values I need to pop up depending on my D selection

example: if D6=windsor E6 should automatically =12.00 (for some reason its 6.00 though..)

Lookup needs a single cell for the lookup value not a range:

=LOOKUP($D6,$D$44:$D$53,$E$44:$E$53)

You need the "$" with $D$44:$D$53,$E$44:$E$53 to anchor the addresses so that they don't increment as you drag the formula down the rows.

To avoid the #NA errors use:
column E:

=IFERROR(LOOKUP($D6,$D$44:$D$53,$E$44:$E$53),"")

column G:

=IF(D6<>"",E6*F6,"")

The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.

Notice in the attachment, that I have used named ranges as follows:
Destination =Sheet1!$D$44:$D$52
RateList =Sheet1!$E$44:$E$52


So the formula in column E is:

=IFERROR(LOOKUP(D6,Destination,RateList),"")

edit: unessasary post

There are several ways:
1. Select the cells to be named, e.g. D44:D52, then type the desired name into the Name Box, or

2. Select the cells, click formulas tab on the ribbon, Define Name on the ribbon type the desired name in the New Name dialog.