Software

Ask something
Question Locked

Excel help - Data validation tables and VLOOKUP

0 helpful
4 replies

by johnmotson

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I was wondering why my VLOOKUP formula is not working, I am using data validation to select the values I have a table like this,

Smooth concrete or asphalt 1.000
Stone blocks pitched or mortared 0.950
Turf 0.880
Stepped 0.850
One layer of rocks on impermeable base 0.700
Two layers of rock armour 0.550

whenever I select 'stepped' it comes up with a value of 1, and whenever I select 'One layer of rocks on impermeable base' it comes up with N/A

The forumla is

=IF(C18<>"",VLOOKUP(C18,F64:G69,2),"")
Where c18 is the data validation drop down menu

Any help will be gratefully recieved

4 replies

by locolobo

=IF(C18<>"",VLOOKUP(C18,F64:G69,2,FALSE),"")

The FALSE is for the range lookup value. You shouldn't need it but for some reason I had the same problem.

by absolutely

The "range" feature is useful if any value
between 0.85 - 1.0 should lookup "asphalt"
for example. For discrete values, not
ranges, the range NEEDS to be set to false.
Obnoxious because it makes extra work to
use vlookup the way that is most common,
but also encourages learning the "range"
function, which could be very handy,
occasionally. Whatever.

There is also one more thing to add to your
function: [u]Absolute[/u] cell references!

=IF(C18<>"",VLOOKUP(C18,$F$64:$G$69,2),"")

It's important to put the absolute
markers '$' in the lookup table, otherwise
somewhere around C23 or C24, you'll be
looking up 'NULL' and 'NULL' in F70 and
G76.

by locolobo

When I do VLOOKUP I tend to test it if the range lookup works wrong I change and try again.

Totally forgot about the absolute references. Done that a few times! :)

This discussion is closed to new replies.