| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP function yielding a #N/A result
Due the size restriction of the IF function, I have used the LOOKUP function
for my conditional formula. It is yielding the results I want, but when the criteria does not match, the result is the error #N/A. Here is the formula I am using: =LOOKUP(B4,{"Name1","Name2","Name3","Name4","Name5 ","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"}) Is there anyway to use the IF function so that if the result of the above formula = #N/A, the cell is blank. Or can I set a conditional format so that if it's #N/A, the cell is highlighted or font color different? Also, with this same formula, is it possible to have the LOOKUP reference more than 1 cell (i.e. =LOOKUP(B4 &" "& B5, . . . .)? Thanks for your help. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP function yielding a #N/A result
Thanks, Don.
I am still having a problem with the overall formula. When I copy the formula down the column, some of the cells that do not meet the criteria are still yielding the result of "VIP". For example: cell B4 does not equal "Name1", but it is still yielding the result "#1 VIP". In my formula =LOOKUP(B4,{"Name1","Name2","Name3","Name4","Name5 ","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"}) the cell reference B4 is actually a link to another worksheet (i.e. B4 in worksheet2 is actually the formula = 'worksheet1'!F2). Will this impact my LOOKUP function result? "Don Guillett" wrote: try =IF(ISNA(yourformula),"",yourformula) =IF(ISNA(LOOKUP(D4,{"Name1","Name2","Name3","Name4 ","Name5","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"})),"",LOO KUP(D5,{"Name1","Name2","Name3","Name4","Name5","N ame6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"})) -- Don Guillett SalesAid Software "MsBeverlee" wrote in message ... Due the size restriction of the IF function, I have used the LOOKUP function for my conditional formula. It is yielding the results I want, but when the criteria does not match, the result is the error #N/A. Here is the formula I am using: =LOOKUP(B4,{"Name1","Name2","Name3","Name4","Name5 ","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"}) Is there anyway to use the IF function so that if the result of the above formula = #N/A, the cell is blank. Or can I set a conditional format so that if it's #N/A, the cell is highlighted or font color different? Also, with this same formula, is it possible to have the LOOKUP reference more than 1 cell (i.e. =LOOKUP(B4 &" "& B5, . . . .)? Thanks for your help. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP function yielding a #N/A result
your problem is that lookup does not look for exact matches
I would recommend something like =if(isna(vlookup(b4,{"name1","name2",...,"name9";" #1 VIP","#2 VIP",...,"VIP"},2,0)),"",vlookup(b4,{"name1","name 2",...,"name9";"#1 VIP","#2 VIP",...,"VIP"},2,0)) This will give you exact matches only. if the list is volitile, I would recomend using a range rather than keeping everythingin the formula "MsBeverlee" wrote: Thanks, Don. I am still having a problem with the overall formula. When I copy the formula down the column, some of the cells that do not meet the criteria are still yielding the result of "VIP". For example: cell B4 does not equal "Name1", but it is still yielding the result "#1 VIP". In my formula =LOOKUP(B4,{"Name1","Name2","Name3","Name4","Name5 ","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"}) the cell reference B4 is actually a link to another worksheet (i.e. B4 in worksheet2 is actually the formula = 'worksheet1'!F2). Will this impact my LOOKUP function result? "Don Guillett" wrote: try =IF(ISNA(yourformula),"",yourformula) =IF(ISNA(LOOKUP(D4,{"Name1","Name2","Name3","Name4 ","Name5","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"})),"",LOO KUP(D5,{"Name1","Name2","Name3","Name4","Name5","N ame6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"})) -- Don Guillett SalesAid Software "MsBeverlee" wrote in message ... Due the size restriction of the IF function, I have used the LOOKUP function for my conditional formula. It is yielding the results I want, but when the criteria does not match, the result is the error #N/A. Here is the formula I am using: =LOOKUP(B4,{"Name1","Name2","Name3","Name4","Name5 ","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"}) Is there anyway to use the IF function so that if the result of the above formula = #N/A, the cell is blank. Or can I set a conditional format so that if it's #N/A, the cell is highlighted or font color different? Also, with this same formula, is it possible to have the LOOKUP reference more than 1 cell (i.e. =LOOKUP(B4 &" "& B5, . . . .)? Thanks for your help. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP function yielding a #N/A result
Thanks, BJ -
However, it still isn't working. Basically what I'm trying to do is create a formula in cell E4 that IF cell B4="Name1" the result will be "#1 VIP", IF B4="Name2", result is "#2 VIP", and so forth. I tried using the VLOOKUP function as you suggested, but it appears column reference "2" is generating the second name in my array (i.e. "Name2"). Thanks! "bj" wrote: your problem is that lookup does not look for exact matches I would recommend something like =if(isna(vlookup(b4,{"name1","name2",...,"name9";" #1 VIP","#2 VIP",...,"VIP"},2,0)),"",vlookup(b4,{"name1","name 2",...,"name9";"#1 VIP","#2 VIP",...,"VIP"},2,0)) This will give you exact matches only. if the list is volitile, I would recomend using a range rather than keeping everythingin the formula "MsBeverlee" wrote: Thanks, Don. I am still having a problem with the overall formula. When I copy the formula down the column, some of the cells that do not meet the criteria are still yielding the result of "VIP". For example: cell B4 does not equal "Name1", but it is still yielding the result "#1 VIP". In my formula =LOOKUP(B4,{"Name1","Name2","Name3","Name4","Name5 ","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"}) the cell reference B4 is actually a link to another worksheet (i.e. B4 in worksheet2 is actually the formula = 'worksheet1'!F2). Will this impact my LOOKUP function result? "Don Guillett" wrote: try =IF(ISNA(yourformula),"",yourformula) =IF(ISNA(LOOKUP(D4,{"Name1","Name2","Name3","Name4 ","Name5","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"})),"",LOO KUP(D5,{"Name1","Name2","Name3","Name4","Name5","N ame6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"})) -- Don Guillett SalesAid Software "MsBeverlee" wrote in message ... Due the size restriction of the IF function, I have used the LOOKUP function for my conditional formula. It is yielding the results I want, but when the criteria does not match, the result is the error #N/A. Here is the formula I am using: =LOOKUP(B4,{"Name1","Name2","Name3","Name4","Name5 ","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"}) Is there anyway to use the IF function so that if the result of the above formula = #N/A, the cell is blank. Or can I set a conditional format so that if it's #N/A, the cell is highlighted or font color different? Also, with this same formula, is it possible to have the LOOKUP reference more than 1 cell (i.e. =LOOKUP(B4 &" "& B5, . . . .)? Thanks for your help. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP function yielding a #N/A result
change Vlookup to Hlookup
I am not sure what I am doing wrong, Cause I normally use Vlookup in this situation, and it works, Today vlookup didn' work but Hlookup does. "MsBeverlee" wrote: Thanks, BJ - However, it still isn't working. Basically what I'm trying to do is create a formula in cell E4 that IF cell B4="Name1" the result will be "#1 VIP", IF B4="Name2", result is "#2 VIP", and so forth. I tried using the VLOOKUP function as you suggested, but it appears column reference "2" is generating the second name in my array (i.e. "Name2"). Thanks! "bj" wrote: your problem is that lookup does not look for exact matches I would recommend something like =if(isna(vlookup(b4,{"name1","name2",...,"name9";" #1 VIP","#2 VIP",...,"VIP"},2,0)),"",vlookup(b4,{"name1","name 2",...,"name9";"#1 VIP","#2 VIP",...,"VIP"},2,0)) This will give you exact matches only. if the list is volitile, I would recomend using a range rather than keeping everythingin the formula "MsBeverlee" wrote: Thanks, Don. I am still having a problem with the overall formula. When I copy the formula down the column, some of the cells that do not meet the criteria are still yielding the result of "VIP". For example: cell B4 does not equal "Name1", but it is still yielding the result "#1 VIP". In my formula =LOOKUP(B4,{"Name1","Name2","Name3","Name4","Name5 ","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"}) the cell reference B4 is actually a link to another worksheet (i.e. B4 in worksheet2 is actually the formula = 'worksheet1'!F2). Will this impact my LOOKUP function result? "Don Guillett" wrote: try =IF(ISNA(yourformula),"",yourformula) =IF(ISNA(LOOKUP(D4,{"Name1","Name2","Name3","Name4 ","Name5","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"})),"",LOO KUP(D5,{"Name1","Name2","Name3","Name4","Name5","N ame6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"})) -- Don Guillett SalesAid Software "MsBeverlee" wrote in message ... Due the size restriction of the IF function, I have used the LOOKUP function for my conditional formula. It is yielding the results I want, but when the criteria does not match, the result is the error #N/A. Here is the formula I am using: =LOOKUP(B4,{"Name1","Name2","Name3","Name4","Name5 ","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"}) Is there anyway to use the IF function so that if the result of the above formula = #N/A, the cell is blank. Or can I set a conditional format so that if it's #N/A, the cell is highlighted or font color different? Also, with this same formula, is it possible to have the LOOKUP reference more than 1 cell (i.e. =LOOKUP(B4 &" "& B5, . . . .)? Thanks for your help. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
LOOKUP function yielding a #N/A result
Hi, BJ! It worked with the HLOOKUP! Thanks so much!
Now . . . another question. Is it possible to use this same formula but have it lookup 2 separate columns? For example, I need it to look up both the First Name (which is in cell $A2) and the Last Name(which is in cell $B2), and then if both of these match, return the results I want (i.e. "VIP"): Can this be done? Thanks! "bj" wrote: change Vlookup to Hlookup I am not sure what I am doing wrong, Cause I normally use Vlookup in this situation, and it works, Today vlookup didn' work but Hlookup does. "MsBeverlee" wrote: Thanks, BJ - However, it still isn't working. Basically what I'm trying to do is create a formula in cell E4 that IF cell B4="Name1" the result will be "#1 VIP", IF B4="Name2", result is "#2 VIP", and so forth. I tried using the VLOOKUP function as you suggested, but it appears column reference "2" is generating the second name in my array (i.e. "Name2"). Thanks! "bj" wrote: your problem is that lookup does not look for exact matches I would recommend something like =if(isna(vlookup(b4,{"name1","name2",...,"name9";" #1 VIP","#2 VIP",...,"VIP"},2,0)),"",vlookup(b4,{"name1","name 2",...,"name9";"#1 VIP","#2 VIP",...,"VIP"},2,0)) This will give you exact matches only. if the list is volitile, I would recomend using a range rather than keeping everythingin the formula "MsBeverlee" wrote: Thanks, Don. I am still having a problem with the overall formula. When I copy the formula down the column, some of the cells that do not meet the criteria are still yielding the result of "VIP". For example: cell B4 does not equal "Name1", but it is still yielding the result "#1 VIP". In my formula =LOOKUP(B4,{"Name1","Name2","Name3","Name4","Name5 ","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"}) the cell reference B4 is actually a link to another worksheet (i.e. B4 in worksheet2 is actually the formula = 'worksheet1'!F2). Will this impact my LOOKUP function result? "Don Guillett" wrote: try =IF(ISNA(yourformula),"",yourformula) =IF(ISNA(LOOKUP(D4,{"Name1","Name2","Name3","Name4 ","Name5","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"})),"",LOO KUP(D5,{"Name1","Name2","Name3","Name4","Name5","N ame6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"})) -- Don Guillett SalesAid Software "MsBeverlee" wrote in message ... Due the size restriction of the IF function, I have used the LOOKUP function for my conditional formula. It is yielding the results I want, but when the criteria does not match, the result is the error #N/A. Here is the formula I am using: =LOOKUP(B4,{"Name1","Name2","Name3","Name4","Name5 ","Name6","Name8","Name9"},{"#1 VIP","#2 VIP","VIP","VIP","VIP","VIP","VIP","VIP"}) Is there anyway to use the IF function so that if the result of the above formula = #N/A, the cell is blank. Or can I set a conditional format so that if it's #N/A, the cell is highlighted or font color different? Also, with this same formula, is it possible to have the LOOKUP reference more than 1 cell (i.e. =LOOKUP(B4 &" "& B5, . . . .)? Thanks for your help. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I turn the #N/A result from a lookup function to a zero? | Excel Worksheet Functions | |||
| Lookup for more than one result | Excel Discussion (Misc queries) | |||
| Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
| I need a Lookup to return more than 1 result | Excel Worksheet Functions | |||
| Unwanted LOOKUP() Result | Excel Discussion (Misc queries) | |||
Linear Mode
