vlookup cell data in range

[dji]

Hi all
I could not see if this question was asked previously. In my range i want to change only the cell data that is relevant but it wants to override existing data in the column range and show a na where there is no corresponding data. All help is appreciated. Perhaps i need to put something in the formula so it ignores a cell that is not in the lookup table.

Thanks and Merry Christmas

 

[FDibbins]

It sounds like you may using an excel "table"

what is the formula, and what do you want to change it to?

 

[dji]

Hi Ford
row amount
100 $10
101 $10
102 $10
103 $10
104 $10
so when i reference the row 100,102,104 to change to $5 and drag the formula down it change 101 and 103 to show #NA , I would like it to leave the existing value in the cell if it doesnt see the row # in the lookup data.

thanks

 

[abdulads]

Please use constant for reference suppose your row number A100 use $A$100 this will keep the data constant.

 

[dji]

hi can you show me a link to how that works or give me an example

thanks

 

[abdulads]

Hi Please try using these formula it might help you achieve your goal[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Rows[/TD]
[TD]Amount[/TD]
[TD]Result Column[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]10[/TD]
[TD]=[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]IF(OR(A2=100,A2=101,A2=104),5,B2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD]10[/TD]
[TD]Drag Down[/TD]
[/TR]
[TR]
[TD]102[/TD]
[TD]10[/TD]
[TD]Drag Down[/TD]
[/TR]
[TR]
[TD]103[/TD]
[TD]10[/TD]
[TD]Drag Down[/TD]
[/TR]
[TR]
[TD]104[/TD]
[TD]10[/TD]
[TD]Drag Down[/TD]
[/TR]
</tbody>[/TABLE]


regards,
Abdul