Return of blank cell if lookup fails
I have a lookup formula and I want to do two things with it.
Formula =LOOKUP(C251,Data!B:B,Data!A:A) When cell C251 is blank it currently returns #N/A, I would like to return a blank cell. Next when a number entered into cell C251 is not found in the specified range (Data!B:B) it returns the next lowest number. (Cell Data!B:B is a list of 7 digit numbers, but some cells contain words. Cell Data!A:A is a list of the names assigned to the number in Data!A:A) I would like it to return just a blank cell. I guess it's the same problem with both, I want it be blank unless it matches something in the list. -- Thanks TimM |
Return of blank cell if lookup fails
On Wed, 23 Nov 2005 07:48:31 -0800, "TimM"
wrote: I have a lookup formula and I want to do two things with it. Formula =LOOKUP(C251,Data!B:B,Data!A:A) When cell C251 is blank it currently returns #N/A, I would like to return a blank cell. Next when a number entered into cell C251 is not found in the specified range (Data!B:B) it returns the next lowest number. (Cell Data!B:B is a list of 7 digit numbers, but some cells contain words. Cell Data!A:A is a list of the names assigned to the number in Data!A:A) I would like it to return just a blank cell. I guess it's the same problem with both, I want it be blank unless it matches something in the list. Wrap it up wiath an IF(ISNA) and include another test. i.e. =IF(ISNA(OR(VLOOKUP(C251,Data!B:B,1,FALSE),LOOKUP( C251,Data!B:B,Data!A:A))),"",LOOKUP(C251,Data!B:B, Data!A:A)) HTH Richard Buttrey __ |
Return of blank cell if lookup fails
=IF(ISNA(MATCH(C251,Data!B:B)),"",INDEX(Data!A:A,M ATCH(C251,Data!B:B,Data!A:
A))) -- HTH RP (remove nothere from the email address if mailing direct) "TimM" wrote in message ... I have a lookup formula and I want to do two things with it. Formula =LOOKUP(C251,Data!B:B,Data!A:A) When cell C251 is blank it currently returns #N/A, I would like to return a blank cell. Next when a number entered into cell C251 is not found in the specified range (Data!B:B) it returns the next lowest number. (Cell Data!B:B is a list of 7 digit numbers, but some cells contain words. Cell Data!A:A is a list of the names assigned to the number in Data!A:A) I would like it to return just a blank cell. I guess it's the same problem with both, I want it be blank unless it matches something in the list. -- Thanks TimM |
Return of blank cell if lookup fails
Try this:
=IF(C251="","",LOOKUP(C251,Data!B:B,Data!A:A) Regards, Paul "TimM" wrote in message ... I have a lookup formula and I want to do two things with it. Formula =LOOKUP(C251,Data!B:B,Data!A:A) When cell C251 is blank it currently returns #N/A, I would like to return a blank cell. Next when a number entered into cell C251 is not found in the specified range (Data!B:B) it returns the next lowest number. (Cell Data!B:B is a list of 7 digit numbers, but some cells contain words. Cell Data!A:A is a list of the names assigned to the number in Data!A:A) I would like it to return just a blank cell. I guess it's the same problem with both, I want it be blank unless it matches something in the list. -- Thanks TimM |
Return of blank cell if lookup fails
You can use something like this to avoid the N/A's : =IF(ISNA(LOOKUP ARGUMENT),"",LOOKUP ARGUMENT)) It basically says if the lookup argument is N/A, then put "" (shows up as blank), otherwise if it's not N/A, then use the argument. I either use the vlookup or the hlookup. If you can switch your data around, then you can specify using the exact value in the argument. =VLOOKUP(A1,B1:C100,2,FALSE) It says to find A1 in the table B1:C100 (what you put in A1 will only be searched for in the range B1:B100, the left-most column of the table), and give me the second cell value to the right. The formula counts B as one, then C as two. The "FALSE" tells it to only look for A1 as an exact match. Hope it helps. Phillycheese -- Phillycheese5 ------------------------------------------------------------------------ Phillycheese5's Profile: http://www.excelforum.com/member.php...o&userid=24196 View this thread: http://www.excelforum.com/showthread...hreadid=487664 |
All times are GMT +1. The time now is 04:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com