Adjusting Vlookup Values
 

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


--
addie
------------------------------------------------------------------------
addie's Profile: http://www.excelforum.com/member.php...o&userid=25526
View this thread: http://www.excelforum.com/showthread...hreadid=392498

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 SpecialValues

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

--
Regards

Roger Govier


"addie" wrote in
message ...

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


--
addie
------------------------------------------------------------------------
addie's Profile:
http://www.excelforum.com/member.php...o&userid=25526
View this thread: http://www.excelforum.com/showthread...hreadid=392498