| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup function help please
I'm trying to use the following LookUp function but it keeps on coming up as
#N/A for option 1 but seems to work for 2,3 and 4, can someone tell me where I'm going wrong =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) The AU17 cell has the following formula in it, and it works - this is for VAT =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) Many Thanks for your help |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function help please
Remove the quotations marks around the 1 in AU17's formula:
=IF(VC1="U",1, IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) -- Best Regards, Luke M "Colette" wrote in message ... I'm trying to use the following LookUp function but it keeps on coming up as #N/A for option 1 but seems to work for 2,3 and 4, can someone tell me where I'm going wrong =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) The AU17 cell has the following formula in it, and it works - this is for VAT =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) Many Thanks for your help |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function help please
Remove all the quotes from around any numbers.
=IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) =IF(VC1="U",1,IF(VC1="Z",2,IF(VC1="E",3,IF(VC1="P" ,4)))) =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) =LOOKUP(AU17,{1,2,3,4},{0.175,0,0,0.05}) Alternative to LOOKUP: =CHOOSE(AU17,0.175,0,0,0.05) -- Biff Microsoft Excel MVP "Colette" wrote in message ... I'm trying to use the following LookUp function but it keeps on coming up as #N/A for option 1 but seems to work for 2,3 and 4, can someone tell me where I'm going wrong =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) The AU17 cell has the following formula in it, and it works - this is for VAT =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) Many Thanks for your help |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function help please
Your problem is the quote marks around the "1". Quotes make the cell text.
Without quotes it's a number, and you need a number in your Lookup statement. So change AU17 to: =IF(VC1="U",1,IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,"")))) I also changed your last IF statement to return nothing ("") if the condition is not met. Otherwise you would get FALSE. The fact that there are spaces in the IF statement shown tells us you manually typed the formula into your e-mail. This is fraught with error. Always cut and paste your formula, to avoid typing errors. Finally, it's very likely your Lookup formula should be: =LOOKUP(AU17,{1,2,3,4},{.175,0,0,0.05}) Regards, Fred "Colette" wrote in message ... I'm trying to use the following LookUp function but it keeps on coming up as #N/A for option 1 but seems to work for 2,3 and 4, can someone tell me where I'm going wrong =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) The AU17 cell has the following formula in it, and it works - this is for VAT =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) Many Thanks for your help |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function help please
Colette -
You had 1 being entered as text into AU17. Try this: =IF(VC1="U",1, IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) -- Daryl S "Colette" wrote: I'm trying to use the following LookUp function but it keeps on coming up as #N/A for option 1 but seems to work for 2,3 and 4, can someone tell me where I'm going wrong =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) The AU17 cell has the following formula in it, and it works - this is for VAT =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) Many Thanks for your help |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function help please
Thanks again for all suggestions...I'd been tearing my hair out for something
that was 'staring me in the face' Colette "Colette" wrote: I'm trying to use the following LookUp function but it keeps on coming up as #N/A for option 1 but seems to work for 2,3 and 4, can someone tell me where I'm going wrong =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) The AU17 cell has the following formula in it, and it works - this is for VAT =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) Many Thanks for your help |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function help please
Thanks Daryl S
Colette "Daryl S" wrote: Colette - You had 1 being entered as text into AU17. Try this: =IF(VC1="U",1, IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) -- Daryl S "Colette" wrote: I'm trying to use the following LookUp function but it keeps on coming up as #N/A for option 1 but seems to work for 2,3 and 4, can someone tell me where I'm going wrong =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) The AU17 cell has the following formula in it, and it works - this is for VAT =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) Many Thanks for your help |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function help please
Many Thanks Luke
Colette "Luke M" wrote: Remove the quotations marks around the 1 in AU17's formula: =IF(VC1="U",1, IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) -- Best Regards, Luke M "Colette" wrote in message ... I'm trying to use the following LookUp function but it keeps on coming up as #N/A for option 1 but seems to work for 2,3 and 4, can someone tell me where I'm going wrong =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) The AU17 cell has the following formula in it, and it works - this is for VAT =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) Many Thanks for your help . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function help please
Many Thanx Fred
Colette "Fred Smith" wrote: Your problem is the quote marks around the "1". Quotes make the cell text. Without quotes it's a number, and you need a number in your Lookup statement. So change AU17 to: =IF(VC1="U",1,IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,"")))) I also changed your last IF statement to return nothing ("") if the condition is not met. Otherwise you would get FALSE. The fact that there are spaces in the IF statement shown tells us you manually typed the formula into your e-mail. This is fraught with error. Always cut and paste your formula, to avoid typing errors. Finally, it's very likely your Lookup formula should be: =LOOKUP(AU17,{1,2,3,4},{.175,0,0,0.05}) Regards, Fred "Colette" wrote in message ... I'm trying to use the following LookUp function but it keeps on coming up as #N/A for option 1 but seems to work for 2,3 and 4, can someone tell me where I'm going wrong =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) The AU17 cell has the following formula in it, and it works - this is for VAT =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) Many Thanks for your help . |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function help please
Many Thanks T. Valko
Colette "T. Valko" wrote: Remove all the quotes from around any numbers. =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) =IF(VC1="U",1,IF(VC1="Z",2,IF(VC1="E",3,IF(VC1="P" ,4)))) =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) =LOOKUP(AU17,{1,2,3,4},{0.175,0,0,0.05}) Alternative to LOOKUP: =CHOOSE(AU17,0.175,0,0,0.05) -- Biff Microsoft Excel MVP "Colette" wrote in message ... I'm trying to use the following LookUp function but it keeps on coming up as #N/A for option 1 but seems to work for 2,3 and 4, can someone tell me where I'm going wrong =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) The AU17 cell has the following formula in it, and it works - this is for VAT =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) Many Thanks for your help . |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup function help please
You're welcome!
-- Biff Microsoft Excel MVP "Colette" wrote in message ... Many Thanks T. Valko Colette "T. Valko" wrote: Remove all the quotes from around any numbers. =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) =IF(VC1="U",1,IF(VC1="Z",2,IF(VC1="E",3,IF(VC1="P" ,4)))) =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) =LOOKUP(AU17,{1,2,3,4},{0.175,0,0,0.05}) Alternative to LOOKUP: =CHOOSE(AU17,0.175,0,0,0.05) -- Biff Microsoft Excel MVP "Colette" wrote in message ... I'm trying to use the following LookUp function but it keeps on coming up as #N/A for option 1 but seems to work for 2,3 and 4, can someone tell me where I'm going wrong =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) The AU17 cell has the following formula in it, and it works - this is for VAT =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) Many Thanks for your help . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combining Lookup function and Sum function | Excel Worksheet Functions | |||
| LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
| Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
| how to combine an IF Function with a lookup function to determine | Excel Worksheet Functions | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Linear Mode
