VLookup problem

[DonV]

I have a spread sheet that I need to make a change to. It uses a VLookup to return a value based on the input from an ajacent cell. The way it works now is the value that is returned is the equipment type. In the ajacent cell we input a number and based on that number in the VLookup, the returned value would be the equipment associated with that number. This worked well until we ran into the problem of more than one piece of equipment having the same number. I thought we could swap the columns so the Vlookup value returned is the number rather than the equipment type. The problem is we need to total the value in the column with the numbers but because some cells are empty except for #N/A because nothing has been entered into the ajacent cell, we get a "Value not available error" until we input the equipment. I'm guessing now that VLookup will not work for this because we are totaling a column the may not have a value in a cell until the ajacent cell has the equipment type.

Old VLookup
40.00 Printer
45.00 Desktop
50.00 Printer
54.00 Equip Install
55.00 Printer/Server
65.00 Laptop
70.00 Laptop
75.00 Laptop
100.00 HDTV
Old VLookup Formula
=VLOOKUP(E3,'Pay Rates'!$A$2:$B$14,2,0)


Testing the New VLookup
Desktop 45.00
Laptop 1 65.00
Laptop Spc 2 70.00
Laptop Spc 3 75.00
Server 55.00
Printer 1 40.00
Printer Spc 2 50.00
Printer Spc 3 54.00
Equip Install 54.00
HDTV 100.00
New VLookup Formula
=VLOOKUP(E3,'Pay Rates'!$A$2:$B$14,2,0)

Using A and B as the columns, we would input the equipment in column A, the VLookup would return a number in column B.
We then need to total column B however some cells will not have a number because the ajacent cell in column A has no equipment. Is there a way to total column B but have it ignore any cells with #N/A?

 

[Mike Fogleman]

Test your VLookup with an IF statement using a formula similar to this:

=IF(ISNA(VLOOKUP(A4,$J$4:$K$13,2,0)),0,VLOOKUP(A4,$J$4:$K$13,2,0))

It will test the VLookup to see if it returns #NA, if True, put 0 in the cell, if False use the Lookup value. Now your SUM will work.

Mike F
I have a spread sheet that I need to make a change to. It uses a VLookup to return a value based on the input from an ajacent cell. The way it works now is the value that is returned is the equipment type. In the ajacent cell we input a number and based on that number in the VLookup, the returned value would be the equipment associated with that number. This worked well until we ran into the problem of more than one piece of equipment having the same number. I thought we could swap the columns so the Vlookup value returned is the number rather than the equipment type. The problem is we need to total the value in the column with the numbers but because some cells are empty except for #N/A because nothing has been entered into the ajacent cell, we get a "Value not available error" until we input the equipment. I'm guessing now that VLookup will not work for this because we are totaling a column the may not have a value in a cell until the ajacent cell has the equipment type.

Old VLookup
40.00 Printer
45.00 Desktop
50.00 Printer
54.00 Equip Install
55.00 Printer/Server
65.00 Laptop
70.00 Laptop
75.00 Laptop
100.00 HDTV
Old VLookup Formula
=VLOOKUP(E3,'Pay Rates'!$A$2:$B$14,2,0)


Testing the New VLookup
Desktop 45.00
Laptop 1 65.00
Laptop Spc 2 70.00
Laptop Spc 3 75.00
Server 55.00
Printer 1 40.00
Printer Spc 2 50.00
Printer Spc 3 54.00
Equip Install 54.00
HDTV 100.00
New VLookup Formula
=VLOOKUP(E3,'Pay Rates'!$A$2:$B$14,2,0)

Using A and B as the columns, we would input the equipment in column A, the VLookup would return a number in column B.
We then need to total column B however some cells will not have a number because the ajacent cell in column A has no equipment. Is there a way to total column B but have it ignore any cells with #N/A?

 

[DonV]

Wow, thanks that did the trick and was easier than I thought it would be.
Test your VLookup with an IF statement using a formula similar to this:

=IF(ISNA(VLOOKUP(A4,$J$4:$K$13,2,0)),0,VLOOKUP(A4,$J$4:$K$13,2,0))

It will test the VLookup to see if it returns #NA, if True, put 0 in the cell, if False use the Lookup value. Now your SUM will work.

Mike F
I have a spread sheet that I need to make a change to. It uses a VLookup to return a value based on the input from an ajacent cell. The way it works now is the value that is returned is the equipment type. In the ajacent cell we input a number and based on that number in the VLookup, the returned value would be the equipment associated with that number. This worked well until we ran into the problem of more than one piece of equipment having the same number. I thought we could swap the columns so the Vlookup value returned is the number rather than the equipment type. The problem is we need to total the value in the column with the numbers but because some cells are empty except for #N/A because nothing has been entered into the ajacent cell, we get a "Value not available error" until we input the equipment. I'm guessing now that VLookup will not work for this because we are totaling a column the may not have a value in a cell until the ajacent cell has the equipment type.

Old VLookup
40.00 Printer
45.00 Desktop
50.00 Printer
54.00 Equip Install
55.00 Printer/Server
65.00 Laptop
70.00 Laptop
75.00 Laptop
100.00 HDTV
Old VLookup Formula
=VLOOKUP(E3,'Pay Rates'!$A$2:$B$14,2,0)


Testing the New VLookup
Desktop 45.00
Laptop 1 65.00
Laptop Spc 2 70.00
Laptop Spc 3 75.00
Server 55.00
Printer 1 40.00
Printer Spc 2 50.00
Printer Spc 3 54.00
Equip Install 54.00
HDTV 100.00
New VLookup Formula
=VLOOKUP(E3,'Pay Rates'!$A$2:$B$14,2,0)

Using A and B as the columns, we would input the equipment in column A, the VLookup would return a number in column B.
We then need to total column B however some cells will not have a number because the ajacent cell in column A has no equipment. Is there a way to total column B but have it ignore any cells with #N/A?