my lookup table gets confused?

[xtcking]

i use a 9 row look up table for my invoice sheet, for some reason when I select a value from my drop down list it doesn't always find the corresponding value. it always find a value.. just not always the adjacent value. for example if I select the D47 value the lookup table presents the E53 value.. it should be D47 =E47 as per my table

[protonLeah]

(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!

[xtcking]

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..)

[protonLeah]

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

column G:

Please Login or Register  to view this content.

[xtcking]

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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

column G:

Please Login or Register  to view this content.

So all I have to do is add the $ to my current code?

[xtcking]

Thank you so much for the IF statements, they made it look so much better. however Im still having trouble with a random value popping into E when selecting in the D column

[protonLeah]

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:

Please Login or Register  to view this content.

[xtcking]

edit: unessasary post

[xtcking]

alright, I looked through your upload. my biggest question is.. how did you bind the 9 destination options in the E forumla simply as Desination and same with rate list

[protonLeah]

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.

[mstwntd]

You could also use a Vlookup. It's a vertical lookup. Commas separate out the requirements.

"vlookup(D6,$D$44:$E$52,2,false)" the first part is what you want to find/match with. The second part is the range you are trying to find that value in. The dollar sign anchors the reference point. The first in on the cell column the second is on the row. You can use F4 to toggle between the anchor options. The third is the column identifier value you want to return when you have a match. The last is either true or false (1/0). This says to either bring back only exact matches (false) or closest match (true). If false and no match it will show an #N/A error message.