# Vlookup problem with a range retreiving correct number when negative

1. ## Vlookup problem with a range retreiving correct number when negative

I am guessing someone will figure this out quickly. In the attachment I have column E & F with percentages. I am doing a vlookup in columns I & L. My question is why does the vlookup give me correct information when the number is positive, but doesn't when negative. Do I need to format something different when the number is negative as in column K? In other words, if I was doing a vlookup of 5% or -5% I should get 2% as the result of my vlookup and currently I am not getting that result.

Thanks,

2. ## Re: Vlookup problem with a range retreiving correct number when negative

If you use VLOOKUP without specifying the 4th parameter, then it is assumed to be TRUE which means that your data has to be sorted in increasing order. Your data is sorted in columne for +ve numbers, and so I2 returns the correct result, but column F (for negative numbers) is not sorted in increasing order, so the formula in L2 gives an incorrect result.

You can use INDEX/MATCH instead, as you can specify how the data is sorted using the 3rd parameter of the MATCH function. Put this in L2:

=INDEX(G4:G12,MATCH(K2,F4:F12,-1))

Hope this helps.

Pete

3. ## Re: Vlookup problem with a range retreiving correct number when negative

That worked perfectly....I was considering to do this before, but wasn't sure if there was an easier way with vlookup…….thank you

4. ## Re: Vlookup problem with a range retreiving correct number when negative

INDEX/MATCH is more flexible than VLOOKUP, as can be seen in this example.

Glad to help, and thanks for the rep.

Pete

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1