Solved: Vlookup and negative numbers

[pedrovarela]

Hi,

In order to show numbers in percentages (from 0,00 to 1,00) in colours I created the following Name (clsValues) where cls are asigned to colours.

0 cls1
0,2 cls2
0,35 cls3
0,5 cls4
0,6 cls5

I used =VLOOKUP(data;clsValues;2;TRUE) and it worked well.

Now I'm trying to assign colours to percentages including negative percentages (from -1,00 to 1,00).

I created a Name called "clsValues" and used "
=VLOOKUP(Data;clsValues;2;TRUE)
"

-0,1 cls1
-0,03 cls2
0,03 cls3
0,03 cls4
0,1 cls5

But I get an error. How can I handle vlookup, and negative %?

Thanks!!!!!!

[Bob Phillips]

It is working fine for me, as I understand what you are saying. Can you post a workbook?

[pedrovarela]

Hi,

Ok. Find attached the workbook. Table2Value is the one that doesn't work.

Thanks!!

[Bob Phillips]

The lookup value 04DIG doesn't exist in Table2Values, so it is no wonder it fails.

[Bob Phillips]

=VLOOKUP(gainvalue,table2Values,2,TRUE)

works fine.

[Bob Phillips]

or even

=VLOOKUP(VLOOKUP(region,Data,3),table2Values,2,TRUE)

[pedrovarela]

Thanks!!!! You were right!