V-Look Up Function

[akbar]

Dear All,
I've attached a spread sheet,please some one have a look on those V-Look function on the rate columns.all i've to get is,when i click OFD rate from the drop down menu, the value 260 should come on fixed rate column,then for OFD/TW Rate, 260 should appear on fixed rate & 65 should appear on the next column,and for TP & VOR the values should be zero.can please some one help me to fix this problem.

Thanks In Advance,

Akbar

[broro183]

hi Akbar,

I'm sure this could be improved (possibly by using named ranges) but the below should work...

Please Login or Register  to view this content.

hth
Rob

[akbar]

Thanks Rob,
Its Working,but how does it function,can you plz explain.

Cheers,
Akbar

[akbar]

Rob,
Please find the attached sheet,i've just added some more columns for data
entry and copied all your formulas over there,but in some cases it is giving me
#N/A error,can you please tell me what is the problem.


Regards,
Akbar

[broro183]

hi Akbar,

Sorry, I made a slight error in my first formula by missing out the last parameter ("range lookup") of the Vlookup function. This meant that the default of true was used, however this rquires the lookup range to be sorted for it to work otherwise it gives N/a's as seen.

Please replace this

Please Login or Register  to view this content.

with the correct version ("false" which is underlined

Please Login or Register  to view this content.

To help learn what formulae do, the best way to understand is usually to break them down & look at a section at a time to figure out what each part does. I'm using Excel 2003 (hopefully you do to b/c I'm not sure how to do this inother versions) & I find the best way to view a section at a time is to select the cell, click in the formula bar & then press the "fx" icon tothe left of the formula bar. This fx icon brings up a dialog box with the separate sections (argument) of each formula & a brief description of what each argument does. From this box you can press the Helkp option to open the Helpfiles whihc may help you understand more.

In this case, the formula is checking if the left 3 characters = "OFD" because this was common to both rates that charged $260 & should show up in the left column. If the left 3 characters match, it spits out $260, if they don't it checks the "if false" part of the formula steps up to play. the "if false" part then completes a vlookup like your original email.


hth
Rob

[akbar]

Thanks Rob,
Its Working Now.

Thanks,
Akbar

[akbar]

Dear Rob,

Thers's one more problem happening around,here in this drop down menu i'm foced to put a value in that,if the menu is empty it is giving a N/A error,is there any way to rectify that.

Thanks In Advance,
Akbar

[broro183]

hi Akbar,

I don't know how many rows of info you'll have, but the following minimises the amount of calculation that the file has to do by separating the formulae into 2 cells.

Insert a new column between col C & col D, this becomes the new column D (& could, for example, be called the Error check column), in the new cell D5 type

Please Login or Register  to view this content.

now in the original D5 which has become Column E5 type

Please Login or Register  to view this content.

.

hth
Rob

[akbar]

Thanks Rob,


Regards,
Akbar