|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
March 12th, 2010, 02:35 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using Iserror with If statement and Vlookup
I have the following formula in a cell:
=IF($K2="STEEL",VLOOKUP($B2,'Macos 12 Mo. Data'!$A$4:$AU$326,40,FALSE),0) Problem is that the results of this are used for calculations and K2 sometimes doesn't equal "STEEL" so I get an error. I've tried putting an Iserror and have the error = 0 but am doing something wrong. Can you help? 
|
|
#2
March 12th, 2010, 02:44 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Iserror with If statement and Vlookup
An error would occur if the VLOOKUP can not find a result. Perhaps this:
=IF($K2="STEEL",IF(ISERROR(VLOOKUP($B2,'Macos 12 Mo. Data'!$A$4:$AU$326,40,FALSE)),0,VLOOKUP($B2,'Macos 12 Mo. Data'!$A$4:$AU$326,40,FALSE)),0) or =IF(OR($K2"STEEL",ISERROR(VLOOKUP($B2,'Macos 12 Mo. Data'!$A$4:$AU$326,40,FALSE))),0,VLOOKUP($B2,'Maco s 12 Mo. Data'!$A$4:$AU$326,40,FALSE)) -- Best Regards, Luke M "juliejg1" wrote in message ... I have the following formula in a cell: =IF($K2="STEEL",VLOOKUP($B2,'Macos 12 Mo. Data'!$A$4:$AU$326,40,FALSE),0) Problem is that the results of this are used for calculations and K2 sometimes doesn't equal "STEEL" so I get an error. I've tried putting an Iserror and have the error = 0 but am doing something wrong. Can you help?
|
|
#3
March 12th, 2010, 02:54 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Iserror with If statement and Vlookup
Hi,
The way the formula is set up if 'steel' isn't in B2 it will return zero. What will give an error is not find the value in B2 in the lookup range and you can get around that by checking if the lookup value is there before executing the vlookup =IF(COUNTIF('Macos 12 Mo. Data'!$A$4:$A$326,$B2)0,IF($K2="STEEL",VLOOKUP($B 2,'Macos 12 Mo. Data'!$A$4:$AU$326,40,FALSE),0),"") -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "juliejg1" wrote: I have the following formula in a cell: =IF($K2="STEEL",VLOOKUP($B2,'Macos 12 Mo. Data'!$A$4:$AU$326,40,FALSE),0) Problem is that the results of this are used for calculations and K2 sometimes doesn't equal "STEEL" so I get an error. I've tried putting an Iserror and have the error = 0 but am doing something wrong. Can you help?
|
|
#4
March 12th, 2010, 03:01 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using Iserror with If statement and Vlookup
Thank you Mike....very helpful! "Mike H" wrote: Hi, The way the formula is set up if 'steel' isn't in B2 it will return zero. What will give an error is not find the value in B2 in the lookup range and you can get around that by checking if the lookup value is there before executing the vlookup =IF(COUNTIF('Macos 12 Mo. Data'!$A$4:$A$326,$B2)0,IF($K2="STEEL",VLOOKUP($B 2,'Macos 12 Mo. Data'!$A$4:$AU$326,40,FALSE),0),"") -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "juliejg1" wrote: I have the following formula in a cell: =IF($K2="STEEL",VLOOKUP($B2,'Macos 12 Mo. Data'!$A$4:$AU$326,40,FALSE),0) Problem is that the results of this are used for calculations and K2 sometimes doesn't equal "STEEL" so I get an error. I've tried putting an Iserror and have the error = 0 but am doing something wrong. Can you help?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
