vlookup and cell formating 2

[modmac]

I have a worksheet where I'm using the vlookup function and it works well. In the row in which the vlookup is being used a value is being returned I then multiply it by another number and get a total. I have several rows that do this. If let say I only use 1 row the other two rows cells show a #N/A and when it multiplies out it returns a #N/A which is O.K. but when I try to sum the totals it returns #N/A The vlookup function returns a dollar value. the sum is also a dollar vaule. The vlookup code is
cell B26 =VLOOKUP(A26,PARTS!A2ARTS!B22872,2,FALSE)

I don't know if it is a cell formatting issue or if vlookup is not the correct function to use. I have tried MATCH and INDEX with no luck.

 

[AnotherHiggins]

The trouble is that you can't sum a text cell, and the #N/A returned by the VLookup is considered text.

So just trap that error in the function like this:

[COLOR=blue white]=If(IsNA(VLOOKUP(A26,PARTS!A2:B22872,2,0)),"",VLOOKUP(A26,PARTS!A2:B22872,2,0))[/color]

You may notice that I also shortened your formula a little. There is no need to repeat the sheet name for both parts of the range, and zero acts as false in the formula (1 acts as true).

BTW, the easiest way to refer to a range on another sheet is to simply change sheets and select the range while typing the formula. Excel does the rest!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[modmac]

That worked, Thank you. Can you tell me why the range values change when I copy the formula down to the cells below. The Range value will increase by 1. for example

the original range was PARTS!A2:B22872 after I copy it or drag it to the next cell the range is now PARTS!A3:B22873 and so on..

 

[AnotherHiggins]

Excel is trying to be helpful. Isn't that nice?

Right now you are using Relative Reference in the ranges. If you change that to Absolute Reference that behavior will disappear.

Highlight the range within the formula, like this:
[COLOR=blue white]=If(IsNA(VLOOKUP(A26,PARTS![COLOR=white blue]A2:B22872[/color],2,0)),"",VLOOKUP(A26,PARTS!A2:B22872,2,0))[/color]

Then press F4. The '$'s indicate Absolute Reference. You want it to look like [COLOR=white blue]$A$2:$B$22872[/color] (with dollar signs in front of both Column references and both Row references).

Repeat for the other range(s) in the formula.

See Excel's help file for more info on Absolute vs Relative References.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[modmac]

That did it, Thanks again.