VLOOKUP return #N/A
If VLOOKUP(A6,B6:C30,3,FALSE) returns #N/A (error value),
can I convert that #N/A into a blank cell? If so, how do I do it? Thanks |
VLOOKUP return #N/A
Hi William,
=IF(ISERROR(VLOOKUP(A6,$B$6:$C$30,2,FALSE)),"", VLOOKUP(A6,$B$6:$C$30,2,FALSE)) Note that the table array in vlookup must be absolute with the $ signs. Also the above is one line even though it may appear as 2 lines in this post. -- Regards, OssieMac "William Beard" wrote: If VLOOKUP(A6,B6:C30,3,FALSE) returns #N/A (error value), can I convert that #N/A into a blank cell? If so, how do I do it? Thanks |
VLOOKUP return #N/A
I meant to add that your column index number can't be greater than the number
of columns in the table array. You have 3 in your example and only 2 columns in the table array. -- Regards, OssieMac "OssieMac" wrote: Hi William, =IF(ISERROR(VLOOKUP(A6,$B$6:$C$30,2,FALSE)),"", VLOOKUP(A6,$B$6:$C$30,2,FALSE)) Note that the table array in vlookup must be absolute with the $ signs. Also the above is one line even though it may appear as 2 lines in this post. -- Regards, OssieMac "William Beard" wrote: If VLOOKUP(A6,B6:C30,3,FALSE) returns #N/A (error value), can I convert that #N/A into a blank cell? If so, how do I do it? Thanks |
VLOOKUP return #N/A
On Fri, 23 Jan 2009 21:22:36 -0800, "William Beard"
wrote: If VLOOKUP(A6,B6:C30,3,FALSE) returns #N/A (error value), can I convert that #N/A into a blank cell? If so, how do I do it? Thanks Are you sure that you want to use that formula? You are trying to look for a value in the third column of a range that is only two columns wide. That will always result in a #N/A. You can not return a blank cell from a worksheet function. But if it is OK to return an empty string, "", that will display as blank, then you may try the following formula: =IF(ISNA( your formula goes here), "", your formula goes here too ) This can be useful for a formult that does not always return #N/A. Hope this helps / Lars-Åke |
VLOOKUP return #N/A
Sorry about the typo.
Your solution fits like a glove. Glad you were there. You'll make me look like a genius. Thank you... "OssieMac" wrote in message ... I meant to add that your column index number can't be greater than the number of columns in the table array. You have 3 in your example and only 2 columns in the table array. -- Regards, OssieMac "OssieMac" wrote: Hi William, =IF(ISERROR(VLOOKUP(A6,$B$6:$C$30,2,FALSE)),"", VLOOKUP(A6,$B$6:$C$30,2,FALSE)) Note that the table array in vlookup must be absolute with the $ signs. Also the above is one line even though it may appear as 2 lines in this post. -- Regards, OssieMac "William Beard" wrote: If VLOOKUP(A6,B6:C30,3,FALSE) returns #N/A (error value), can I convert that #N/A into a blank cell? If so, how do I do it? Thanks |
All times are GMT +1. The time now is 05:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com