Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup problem
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup problem
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 "DonV" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup problem
Wow, thanks that did the trick and was easier than I thought it would be.
"Mike Fogleman" wrote in message . .. 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 "DonV" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP problem | New Users to Excel | |||
Problem w/vlookup | Excel Worksheet Functions | |||
Vlookup problem | Excel Worksheet Functions | |||
vlookup problem | Excel Discussion (Misc queries) | |||
vlookup problem | Excel Programming |