Thread: Is there anyway to use Vlookup for negative values or a range of values?

Hi to all.

I am new to excel and would like to improve my skills. I recently encountered this problem. If i want to put a subject for a negative values, how should i do it?

For example:

lookup_value: -1000
table_array:
<0 - Poor
0-10000 - Good
> 10000 - excellent

is it possible to do this? ALternatively see the (new) attachment for clearer question...

Use formula:

in B16, for example:


=LOOKUP(A16,{-9.99999E+307,0,10000},{"Poor","Good","Excellent"})

To colour "Poor" red...

Select cells and go to Format|Conditional Formatting

Choose Cell Value Is >> Equal to >> enter: ="Poor"

Click Format and choose Red from "Font" tab.

Your explanation seems too ADVANCE for me to know. As i am new to excel, could you express it in more simple terms so that i can understand better? Thanks

or perhaps show me in an attachment how it is being done so that i can explore deeper? Thanks Thanks a million :D

It is actually the vector form of the LOOKUP function ( see XL help for more info) where the ranges are hard-coded instead of for example A1:A5
The first term between braces is the lowest number that XL understands in scientific notation. So the formula looks if A1 is in the interval between the numbers in the first range and picks the corresponding value in .So if A1 is between 0 and 10000 it's in the second interval, and Lookup chooses "Good" which is the second value in the second range

HTH

Originally Posted by excelplshelp1

Your explanation seems too ADVANCE for me to know. As i am new to excel, could you express it in more simple terms so that i can understand better? Thanks

or perhaps show me in an attachment how it is being done so that i can explore deeper? Thanks Thanks a million :D

See attachment..

If you look in B19 and down in my attached sample, you will see the formula I created.

All you have to do is enter any value in A19 and down, and the formula will give you the desired result. And if the result is the word "Poor", then it will turn red.

You don't need to enter those parameters everytime.

Note:

The LOOKUP() formula I used, is similar to a VLOOKUP() formula.

One good thing about Excel is that you don't always necessarily need to reference a specific table using these functions. If the "table" would be small enough (like in your example, you only have 3 lookup values and 3 corresponding results), then you can incorporate these right into the formula instead of creating a side table in the spreadsheet.

As arthurbr pointed out, the items between the { } brackets are the lookup values and the corresponding result values.

Syntax:

=LOOKUP(lookup_value,{lookup items},{corresponding results})

This function works on finding the closest match that is smaller than or equal to the lookup value... so if you enter a number between the lookup items values, then it will return the result that corresponds to inbetween the the lookup items list.

The only things to watch out for is that the lookup items must be in ascending order.

See LOOKUP function in Excel help for more details and examples.