```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,))))

```
 0
Utf
2/24/2010 4:08:04 PM
excel.worksheet.functions 4936 articles. 2 followers.

10 Replies
958 Views

Similar Articles

[PageSpeed] 57

```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" <Colette@discussions.microsoft.com> 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

```
 0
Luke
2/24/2010 4:35:33 PM
```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" <Colette@discussions.microsoft.com> 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

```
 0
T
2/24/2010 4:38:35 PM
```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" <Colette@discussions.microsoft.com> 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

```
 0
Fred
2/24/2010 4:47:11 PM
```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
```
 0
Utf
2/24/2010 5:03:01 PM
```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
```
 0
Utf
2/25/2010 10:25:01 AM
```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
```
 0
Utf
2/27/2010 5:34:01 PM
```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" <Colette@discussions.microsoft.com> 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
>
>
> .
>
```
 0
Utf
2/27/2010 5:35:01 PM
```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" <Colette@discussions.microsoft.com> 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
>
>
> .
>
```
 0
Utf
2/27/2010 5:35:01 PM
```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" <Colette@discussions.microsoft.com> 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
>
> .
>
```
 0
Utf
2/27/2010 5:35:01 PM
```You're welcome!

--
Biff
Microsoft Excel MVP

"Colette" <Colette@discussions.microsoft.com> wrote in message
news:A58E73D1-9833-4E34-AEF1-742D89E02B55@microsoft.com...
> 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" <Colette@discussions.microsoft.com> 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
>>
>>
>> .
>>

```
 0
T
2/27/2010 8:28:57 PM

Similar Artilces:

i need help in my vb project.
hello frinds, i have one problem with my vb project's connectivity and searching. i post my small project very shortly on this group. please help if any one have time for me. its really urgent, details of problem : 1) App.Path is not work propar 2) Serching command button code needed. Thanks for read this mail. Bye. Does your questions pertain to Access, relational database product that is part of Microsoft Office Professional Suite? -- Build a little, test a little. "KIRTI" wrote: >...

Using "fixed" decimal place function data for charts
Is there a way to graph data which utilizes the "fixed" function for decimal places? When I try this it will not graph, as the data has become text or something - even though I've selected number in the cell formats. If you've fixed the number of digits using only number formats, the values remain numeric, and they should chart just fine. This means selecting the range, going to the Formatting menu and selecting Cells, and on the Number tab, selecting Number in the category list, and setting a number of digits. I'm not familiar with a "fixed function". I...

Newbie needs help with new Exchange 2003 installation
I am TOTALLY new to Exchange. We just installed Exchange Server 2003 on a Windows 2003 domain. I have one (test) account set up and it appears to work OK but I keep getting an error message that says "Task "Microsoft Exchange Server" reported error (0x8004010F): 'The operation failed. An object could not be found.' I know, I know...How could I possibly not understand THIS message? I appear to be getting mail but I' can't be sure. How can I check to see what's causing this (and more important...what object it's looking for?) Thanks for any help! -Fran- ...

Need help on re-organizing this website
I have volunteered to try to re-organize the website: http://www.hcci-ks.org before we re-design/re-develop it. It apears to have been last touched with FrontPage 2003. The current maintainer is an Administrative Assistant who understands that she doesn't understand anything. If you import a copy of the website you will discover that it has 1 images sub-directory and a bunch of .htm, .pdf, .doc, .jpg files in the root. If you simply try to move the graphics from the root into the images sub-directory you will discover that you will break things because of duplicate grap...

Help
Hi... I tried to work on Word today for first time on newish Mac and realized none of my Office applications work. When I double click on Word I can see something happening as I get the circle of colours twirling, but half an hour later it still hadn't opened. I just have to do this as quickly as possible as I have a deadline for what I have to do Monday at 12. I've tried reinstalling the software, but that didn't do anything for me. Mind you I know absolutely nothing about installing anything on Mac computers so I could be doing it all wrong. Do any of you have any idea what c...

DASL Filter function?
I am trying to figure out how to use the "undocumented" Today(S) function. Basically I have about 10 search folders that make use of most of these: %yesterday %today %tomorrow %last7days %next7days %lastweek %thisweek %nextweek %lastmonth %thismonth %nextmonth So I am also hoping that these functions also allow for passing of a (S) seconds parameter. The reason I am hoping to do this is to pre-adjust my UTC DateTime field "MileStone1UTC" so that it will be normalized into local date/time. e.g. "http://schemas.microsoft.com/mapi/string/{000...

Opening the help window
I have prototyped something before trying it for real in our application. I have this line in my prototype: ::HtmlHelp(GetSafeHwnd(), cHelpFile, HH_DISPLAY_INDEX, NULL); This opens the help system in a separate window, which I can always find later (with FindWindow) and direct to particular pages. Doing the above in the application gives me an unresolved external error, but I can't find anything to include that I'm not already including. MSDN doesn't mention what to include in the help page for HtmlHelp operation. What should I include? Or what am I missing? "GT&q...

Help with Columns
Hi Everybody: I have this excel spreadsheet from a co-worker and the problem that i'm having is that the columns only go to AK and I want to to go to beyond that. My question is: is there a way to get it to show all the columns like when you start a new speadsheet? joe They might be hidden or restricted by a scroll area macro. Why don't you ask the co-worker??? -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "joe" <ranchan02@hotmail.com> wrote in message news:013b01c34ae5\$d68432a0\$a101280a@phx.gbl... > Hi Everybody: > > I have th...

Help me do this
Hi Everyone, I need some help. I want to use Windows Live mail without signing in or having a hotmail or live account. I use gmail and it works fine. I have exported all my contacts from my live.com email and imported all of my contacts so that is not the problem. I want to import my calendar from my live account. I can't find anyway to do this. If I stop signing in of course my calendar is gone. How can I export my live.com email calendar into windows live desktop client so I don't have to sign in anymore? Thx "T-Man" <a@anon.com> wrote in message...

Help?
Ok this is for all the formulae gurus out there. TIA I have a list in a particular column, lets say column A This is a list of dates I have another cell which uses data validation to reference this list. Say B1 In cell B1 the only acceptable entries are that which are in column A Can anyone supply me with a formula for my data validation which will only allow 8 entries. These 8 entries....appearing in the list in B1 will be 8 weeks in advance of the current date. I am currently using the following formula =OFFSET(O2,MATCH(YEAR(NOW()),YEAR(O2:O200),0)-1,,SUMPRODUCT(--(YEAR(O2:O200)=YE...

Help file show tabs
When accessing HELP from Excel 2000 (Office 2000) the tabs are not shown and when SHOW TABS is selected under the Help menu the help file hangs. A second try at showing the help file is successful. This only happens in Excel. I have tried re-installing Office but that didn't solve the problem. Any suggestions? OS: XP Sp2 ...

Help with conditional formatting?
I am trying to set the conditional formatting on cell C21 so that if the value in cell IS21 is less than 2 the cell changes color. Then I want to copy this down column C so it evaluates across each row. I tried using FormulaIs IS21<2, but (a) it didn't change color, and (b) when I tried to copy down, every row had "IS21", rather than changing rows. How do I approach this? Ed Ed, You should use: =IS21<2 instead of IS21<21 (so, use the equal mark) Jan On Mar 13, 2:08 pm, "jan" <j...@releerf.nl> wrote: > Ed, > > You should use: > &...

HELP
Hi All, I'm currently running Microsoft Excell 2003 on Workstations running WindowsXP. My network is both Windows 2000 and 2003. Suddenly my file names are being changed from the names which were given and are not showing up as numbers. Does anyone have any suggestion why this may be so. Thanks Nik Did you mean "...are NOW showing as numbers." instead of "...are not showing as numbers." If yes, then maybe... When excel saves the file, it saves it as a temporary file with a funny name (8 characters--no extension). If the save is successful, xl will delete t...

need help with charting, scheduling plan
Can anyone get me started in the right direction for this project? I have to follow approximately 250-300 people for a period of 20 months in terms of them coming to a prescribed number of appointments and completing phone calls between the actual visits. Each one of these visits and calls MUST occur within a certain range of time, or in other words, a "window" and the windows are based on their previous appointment date with varying time frame from Visit 1, Visit 2, etc. For example, Visit 2 must be 20-30 days from Visit 1, Visit 3 must be 30-48 days from Visit 2, Visit 4 must be...

limitations to charting functionality
I wonder if anyone has some work-arounds for some of Excel's more annoying limitations re: charts. (1) It seems that the Title text box cannot be resized manually, even though the usual 8 grab handles appear on the fuzzy border when the Title is selected. (2) There seems to be no way to attach additional text data to a chart (eg. explanatory information or a formula). Both of these features (and many more) are available in serious applications like SigmaPlot, and it seems odd that Microsoft can keep releasing new versions of Excel without addressing such obvious oversights. Am I...

I have a contact set up on Exchange 2003 that forwards to a cell phone for alerts generated in our environment. This contact is setup to get notice of several events, all events have separate email address as the "sender" one of the events gets allot of notices. I would like to have this contact not receive mail from the one email address that generated allot of events. I have set up the properties under the exchange general > messaging restrictions, and have authenticated users only and from everyone except the email account I don't want to receive alerts from, BUT when anyon...

Macro help with saving a spreadsheet with date and time in it
Can someone help me with some code that would save a file name as "schedule-mm-dd-yyyy-hh:mm"? Thanks, Alan Alan, how about something like this Sub Save_As() ActiveWorkbook.SaveAs Filename:="Schedule " & Format(Now, "mm-dd-yyyy-hh-mm") & ".xls", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Fee...

Help!
We went live with 4 decimals on our functional currency and we now want to reduce them to 2 decimals. All of our transactions so far are in 4 decimals. Can anyone guide me into doing this? thank you Use the Change Decimal Places window to change the number of decimal places used for keeping quantity or currency amounts for items. You might need to complete this procedure if an item was set up improperly, or if you’re changing the units of measurement for an item. NOTE: If an item is on an unposted transaction, you can’t change its decimal place settings. Ensure you have a complete ba...

Help !
I need to create a data input screen on excel where multiple users at the same time will use them & input data. This data then needs to be stored as a database as well, where i can use it to understand trends Thank you. and the question is ...? <abrahamsaj@gmail.com> wrote in message news:1132155054.927936.191640@z14g2000cwz.googlegroups.com... >I need to create a data input screen on excel where multiple users > at the same time will use them & input data. > This data then needs to be stored as a database as well, where i can > use it to understand trends >...

LOOKUP function help
Hi everyone! I've been tackling excel for the past 2 days at work trying to format a spreadsheet I made that lists accounts associated with different people. I am trying to write up a lookup function that can look up a certain account code from a list of account codes and would ultimately return the people associated with that account number. In this case, let's say that columns A through F have information regarding the person in question and column G contains the account numbers. The array in question ranges from A2 to G549. I essentially need to write a LOOKUP f...

Need help installing Office 2007
I'm trying to install Office 2007 on my Vista PC. A short way through the installation I get this message: " Cannot Find Proofing.en-us”.... I've searched around: http://www.google.ca/search?hl=en&source=hp&q=setup+cannot+find+proofing.en-us+proof.fr+proof.msi&meta=&aq=1&oq=setup+cannot+find+Proofing.en-us\Proof.fr but I can't get anything to work. Any help or suggestions would be very much appreciated. Thank you. Gary What version of Office 2007. Did you have a Trial version already installed on your PC and did you remove it -- Peter ...

Adding lookup to a exsisting form
How can I add a lookup to a form? Clicking design and going to Data gives me options for functions etc...but not for lookup..... On Mon, 17 May 2010 17:39:01 -0700, Terry Cano <TerryCano@discussions.microsoft.com> wrote: >How can I add a lookup to a form? >Clicking design and going to Data gives me options for functions etc...but >not for lookup..... A "Lookup" is also called a "Combo Box", you might find it easier using that term. You're not saying what you want to look up from where, so it's a bit hard to be specific, but check o...

Help with a DL & Ldap query
Hi I am trying to get 15000 user from a distribution list into a recordset. objcommand.commandtext= _ "<LDAP://CN=ZZZ-All Users,OU=DistList,OU=XXX-XXX,DC=XXX,DC=XXX,DC=XXX>;(objectCategory=member); CN " The path is correct and I can get all users from an OU but I cannot get the members of a distribution list to populate the recordset. Thanks for any help - Dave You need to bind to the Distribution Group, get the member multivalued attribute in an array, then iterate through each member in the array to list group members. -- Bharat Suneja MCSE, MCT ---------------...

Index and Match Function in VB
A few days ago, I did receive very good help from Max and Dave to achieve the following formula, =IF(B5="Synth",INDEX(AUTOS!\$K\$10:\$K\$500,MATCH(1,(Autos!\$F\$10:\$F\$500='ID'!D5)*(Autos!\$G\$10:\$G\$400='ID'!\$R\$2)*(AUTOS!\$I\$10:\$I\$400="C"),0))-INDEX(AUTOS!\$K\$10:\$K\$500,MATCH(1,(AUTOS!\$F\$10:\$F\$500='ID'!D5)*(AUTOS!\$G\$10:\$G\$400='ID'!\$R\$2)*(AUTOS!\$I\$10:\$I\$400="P"),0))+\$R\$2-\$R\$5,VLOOKUP(D5,\$P\$4:\$R\$8,3)-\$R\$5) Basically I was looking to retrieve data based on multiple criteria and the formula is working just fine. I now want to also be able to def...

Help with a formula..
I am trying to create a formula that will take information from a cell on one sheet and combine it with text on another sheet. I know how to get the two together. My problem is that I want the part that is brought in to be bolded type. Here is what I have in the formula. ="we are pleased to submit our quotation for "&(cell reference)&" according to the following specifications." What I want to do is have the cell reference part be bold type. Is there a way to do that? It doesnt work if I bold the cell.. already tried it.. Any suggestions? Thanks! KK You'...