|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
Mar 22nd, 2002, 12:53 PM
|
#1 |
|
New Member
Join Date: Mar 2002
Posts: 2
|
I am working with a VLOOKUP formula. How can I subsitute the #N/A into a 0?
I don't want the results to say #N/A if it does not find the value. I want it to show 0. Any help would be appreciated!! Thanks in advanced!! |
|
|
|
Mar 22nd, 2002, 01:00 PM
|
#2 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,317
|
Quote:
=VLOOKUP(A1,$E$2:$G$40,2,FALSE) use: =IF(COUNTIF($E$2:$E$40,A2),VLOOKUP(A1,$E$2:$G$40,2,0),0) If your VLOOKUP formula looks like =VLOOKUP(A1,$E$2:$G$40,2,TRUE) or If your VLOOKUP formula looks like =VLOOKUP(A1,$E$2:$G$40,2) use: =IF(ISNUMBER(MATCH(A2,$E$2:$E$40)),=VLOOKUP(A1,$E$2:$G$40,2),0) |
|
|
|
|
|
Mar 22nd, 2002, 01:04 PM
|
#3 |
|
Board Regular
Join Date: Feb 2002
Location: Calgary, Alberta Canada
Posts: 2,065
|
What formula are you using? Aladin made excellent recommendations just a little earlier. - slightly edited - =IF(AND(LEN(A2),COUNTIF(XXXX,A2)),VLOOKUP(A2,XXXXY,2,0),0) In English, if A2 is blank and A2 is not in range named XXXX, 0. Otherwise lookup A2 in Database XXXXY Credit to Aladin. Please read his comments in Thread(s) on Vlookup. Aladin |
|
|
|
|
Mar 22nd, 2002, 01:09 PM
|
#4 |
|
Board Regular
Join Date: Mar 2002
Location: Arkansas
Posts: 358
|
Or if you dont want to supress your '0's on your worksheet, maby something like this. =IF(COUNTIF($E$2:$E$40,A2),VLOOKUP(A2,$E$2:$G$40,2,0)," ")
|
|
|
|
|
Mar 22nd, 2002, 01:46 PM
|
#5 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: Austin, Texas USA
Posts: 11,654
|
Quote:
=IF(ISNA(A2),0,A2) |
|
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
