Adjusting Vlookup Values

A

addie

Is there an ‘If / Then’ formula that can adjust a vlookup’s value?

When I use “VLOOKUP($A43,'Line Items'!$A$2:$M$6000,5,FALSE)” I
sometimes come across a few negative numbers that appear as “ 1000- “.
How can I revise the vlookup formula to ensure that such negative
numbers appear as “ -1000 ”?

Addie
 
R

Roger Govier

Hi Adie
I think you would be best served by adjusting your source table to correct
the trailing -ves rather than attempting a complication to your VLOOKUP.
In a spare column on the source table, assuming the column containg the
values is column C enter
=IF(RIGHT(C1)<>"-",C1,RIGHT(C1)&LEFT(C1,LEN(C1)-1)
Copy down the column for the full extent of your data.
Copy the new Column, move your cursor to cell C1 and Paste Special>Values

Change ranges to suit.
N.B.Make a BACKUP of your data before you begin!!!
 
Top