VLookup #VALUE! error help needed to resolve

The following is the funcation I have:
=VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0)

I have all the columns formatted the same; as in the column that the 
function is using to lookup is text and so is the column for this figure in 
order to pull back the appropriate answer.  I have keyed the data instead of 
having links.  I have replaced the final '0' with TRUE & FALSE then put it 
back.  I have formatted the columns for text and for numbers.

But I am getting the #VALUE! error in SOME of the cells NOT all of the 
cells.  I don't know what else to do.
0
Utf
2/1/2010 9:58:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
1182 Views

Similar Articles

[PageSpeed] 37

Try this heavier duty index/match, normal ENTER will do:
=INDEX('FA CC Summary Report 
1141'!G$9:G$92,MATCH(TRIM(B10)&"",INDEX(TRIM('FA CC Summary Report 
1141'!F$9:F$92),),0))
Above should yield better results. Success? celebrate it, hit YES below
-- 
Max
Singapore
--- 
"Kristin" wrote:
> The following is the funcation I have:
> =VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0)
> 
> I have all the columns formatted the same; as in the column that the 
> function is using to lookup is text and so is the column for this figure in 
> order to pull back the appropriate answer.  I have keyed the data instead of 
> having links.  I have replaced the final '0' with TRUE & FALSE then put it 
> back.  I have formatted the columns for text and for numbers.
> 
> But I am getting the #VALUE! error in SOME of the cells NOT all of the 
> cells.  I don't know what else to do.
0
Utf
2/1/2010 10:47:01 PM
Still getting #VALUE! error.  Anything else you can think of?

Kristin

"Max" wrote:

> Try this heavier duty index/match, normal ENTER will do:
> =INDEX('FA CC Summary Report 
> 1141'!G$9:G$92,MATCH(TRIM(B10)&"",INDEX(TRIM('FA CC Summary Report 
> 1141'!F$9:F$92),),0))
> Above should yield better results. Success? celebrate it, hit YES below
> -- 
> Max
> Singapore
> --- 
> "Kristin" wrote:
> > The following is the funcation I have:
> > =VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0)
> > 
> > I have all the columns formatted the same; as in the column that the 
> > function is using to lookup is text and so is the column for this figure in 
> > order to pull back the appropriate answer.  I have keyed the data instead of 
> > having links.  I have replaced the final '0' with TRUE & FALSE then put it 
> > back.  I have formatted the columns for text and for numbers.
> > 
> > But I am getting the #VALUE! error in SOME of the cells NOT all of the 
> > cells.  I don't know what else to do.
0
Utf
2/2/2010 5:41:01 PM
Check for residual/formula returned errors (#VALUE!) in your lookup data and 
in your reference col F data. Clean these up and all should be well.
-- 
Max
Singapore
--- 
"Kristin" wrote:
> Still getting #VALUE! error.  Anything else you can think of?

0
Utf
2/2/2010 11:13:01 PM
The formula is now working appropriately even though I don't understand why.  
Thank you for that formula, it is a good one.

I had checked the formatting of all sections prior to sending this question 
out and double checked them yesterday.  Then I copied a cell below where the 
#VALUE! error first occurred which worked for the new figure then I changed 
it back to the figure I wanted to be looked up in the formula.  Now all the 
cells are working accurately.

Kristin
"Max" wrote:

> Check for residual/formula returned errors (#VALUE!) in your lookup data and 
> in your reference col F data. Clean these up and all should be well.
> -- 
> Max
> Singapore
> --- 
> "Kristin" wrote:
> > Still getting #VALUE! error.  Anything else you can think of?
> 
0
Utf
2/3/2010 7:50:01 PM
Reply:

Similar Artilces:

I NEED AN EXCEL FORMULA
I HAVE A COLUMN OF DATES AND I NEED TO SORT BY THE MOST CURRENT DATE AS IT IS UPDATED, WHAT IS THAT FORMULA? As long as they are in true date format, select the data and do Data / Sort / Descending. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- Seasons Greetings and Very Best wishes to all :-) ---------------------------------------------------------------------------- "LITTLE1" <anonymous@discussions.microsoft.com...

Help looking up values!!
Hi all, Hopefully I can explain this well enough to get some help. I have a dataset of whale dive data that contains four columns. The first column is time of day, the second is depth, and the third and fourth are latitutiude and longitude. I am trying to look up the time of day when a certain depth is first reached and when it is last reached. Essentially I want to determine when a whale has reached it's maximum dive depth and when it begins to surface. Is this making sense? Here is an example (lat and long are no important, so I've left them out) assuming I want: 12:01 20 12:...

Need installation guide for installing a two-node sql sever cluste
Where I am working now, an individual did a poor job of setting up an SQL Server Failover cluster and we must blow it away create a new one this weekend. For installation documentation, I am only finding bits and pieces in the books online and the knowledge base. Does anyone know of a book or white paper that will walk you through setting up the cluster from start to finish? Thanks, Steve K. Hi Steve, All the official documentation can be found in the implementation guide: http://www.microsoft.com/downloads/details.aspx?FamilyID=1ceb5e01-de9f-48c0-8ce2-51633ebf4714&DisplayLang=e...

Vlookup will not retrun value
I have a very simple formula =VLOOKUP(M3,Days!$A$5:$I$28,9,FALSE) When I change M3 to N3 and hit enter, I see =VLOOKUP(N3,Days!$A$5:$I$28,9,FALSE) displayed. How do I get this to retun the value and not the text string? Format the cell as General (or anything but text). Then hit F2, then enter (excel will see that it's a formula) Dave Shultz wrote: > > I have a very simple formula > > =VLOOKUP(M3,Days!$A$5:$I$28,9,FALSE) > > When I change M3 to N3 and hit enter, I see > =VLOOKUP(N3,Days!$A$5:$I$28,9,FALSE) displayed. How do I get this to ret...

Axis values showing incomplete
I have a chat that represents the weekly fulfillment of a budget. In consists of a line replesenting budget level and weekly I add in a separate worksheet the orders level for the current week. The thing is that the time axis, (readsthe values from Budget line sheet) is only showing month and a dash, but not the year for the months where I dont have orders values. That is: It's shows "Oct-" for values beyond Sep where I don't have Orders information where It should be "Oct-05" Pls help, I have tried every properties for charts but no good results Thanks, fro...

Erroneous 'Enter Parameter Value' dialog after converting to Acc 2
After converting from access 97 to access 2000 when I try to open some of my reports from code I get an 'Enter Parameter Value' dialog appearing. There is no field name listed in the dialog and when I press the OK button the reports appears just as in Access 97. Is there any way to suppress this dialog from appearing? Answered in m.p.a.reports ...

outlook error message #41
I have installed office 2003 and when I try to get to my address book I get the following message "can't contact LDAP directory server(81)". How do I repair this ...

Some Error that I can't figure out.
Ok I am typing in a bunch of UPC numbers for my data sheet, It will be a couple hundred numbers. The UPC numbers are as follows 012569528628 027616149237 025192051029 012235111536 012569528734 012569508422 12569508439 86162180934 043396039490 043396603820 012569509023 012569509030 12569512429 12569512436 For some odd reason all the numbers without a zero are wrong. All of these numbers need 1 number 0 in front of them. As for the other numbers I get a little green arrow in the upper left corner of the cell. It tells me Something about "Number stored as text" (This is the one...

Help!!! All Products Frozen on Startup
I have just installed the latest service pack located by the AutoUpdate utility. Now, I can't open any of the Office products - they all freeze during startup. They freeze on the splash screen when Configuring Office Components... VisualBasic for Applications is displayed. Please help me!! I can't do anything!! None of my documents are accessible and I am dead in the water. I am running Office:Mac 2004 Student and Teacher Edition on a Macintosh G4 running OS X 10.4.3. If I need to reinstall from CD, will it take another of my product keys to do it or can I use the one that I pre...

Help with query searching within a string
I want to enter data character by character into a combo box and search the field for matching records. Ex: if I type in "a" any records containing the letter a anywhwere in the string. If I then type "h" the list will be reduced to any records containing "ah" within the string. etc etc. I assume this will go in the criteria box under the field I am searching. Is this possible? Thanks Brad Brad "within the string" is different than "starts with". A combobox can use the AutoComplete property to help you find a row that "starts...

linker error because of unresolved external symbol
Hi there, I have a linker error I cannot get rid of. Here it is: nafxcw.lib(appcore.obj) : error LNK2001: unresolved external symbol ___argv nafxcw.lib(appcore.obj) : error LNK2001: unresolved external symbol ___argc nafxcw.lib(filelist.obj) : error LNK2001: unresolved external symbol __mbctype nafxcw.lib(apphelp.obj) : error LNK2001: unresolved external symbol __mbctype nafxcw.lib(timecore.obj) : error LNK2001: unresolved external symbol __mbctype I have been searching the newsgroups and haven't found a solution for me. Usually , one get this error when mixing up run-time libs. But I...

Need to get Publisher 2003 back, vs. 2007
I have Office 2003. I had Word 2003 and Excel 2003 loaded, but Publisher 2003 was not. I "repaired" Office by inserting disc. I now have Pub 2007! I have docs created in 03, they will not open. Plus, I cannot even open Pub 07 at all--I get a statement something like " Will only work when loaded" (approximate statement). All in all, I am very frustrated. First, can I revert back to Pub 03 somehow? That is my preference. Thank you! "Cockapoo Lover" <Cockapoo Lover@discussions.microsoft.com> wrote in message news:9F3F8A80-5640-4E51-...

multiple value choices for If range().value = "xxx"
The macro below runs on worksheet change and works properly. However, I have 5 values to examine in column H. The values are: Split, Payday 1, Payday 2, Payday 3, and Payday 4. Any of these choices should result in unlocking and clearing the cells in that row (first part of if statement). Otherwise, the range 'pasterage' is copied to that row and it is locked again. I can't find the proper syntax (if there is any) to say something like is possible in SQL (if ...value in ("Split","Payday 1",...)) Please advise on the best way to go about this. ...

Sheet Name Refering to Cell Value
I want to name a sheet to the cell value in A2. Cell A2 has an Alt-enter in the cell. I want the sheet name to be the cell value of the first line only. Can anybody help. TIA Greg One way: Option Explicit Sub testme01() Dim myStr As String Dim vbLFPos As Long With ActiveSheet myStr = .Range("a2").Value vbLFPos = InStr(1, myStr, vblf) If vbLFPos > 0 Then myStr = Left(myStr, vbLFPos - 1) On Error Resume Next .Name = myStr If Err.Number <> 0 Then MsgBox "couldn...

Need help change a particular word in an XML doc
Hello, I have an XML doc and I am trying to write a utility thath will allow me to change a particular word with my "title" section of theXML doc. I would like to look for a "word" in the title and then change only that "word" to something else. Below in XML file I would want to change "Issue" to "Concern". This is one snip of the XML file there may be many more instances of the word "Issue" that I would want to change. I know how to walk through the file and find the whole instance of a string, ie Add/Raise Issue and then repla...

can't sum figures in a report ...help please
I have a text box that uses an if statement to determine whether someone is absent in my class or not. I name the text box inattendance. I have a text box called points that assigns points to absent students depending on which class they are currently enrolled in. I want to sum the total points for each student. I do a simple =sum([points]) and when I run the report I get a box that just says points indicating that it doesn't have a definition for points. I used the name property of the text box and called it points. any suggestions on alernate methods or on what I am do...

Run-time error 3077
Hello Everybody, In Access 2003, I am using VBA to find a string in a table. Every time I try to execute following line: anRST.FindFirst "John Smith" I get "Run-time error '3077':" which says: Syntax error (missing operator) in expression. Same is true for FindNext, FindLast and FindPrevious. anRST was defined using following code: Set anRST = CurrentDb.OpenRecordset("Test", dbOpenDynaset) Where "Test" is the name of the table. Any ideas what I am missing? Thank you, -- Syed Zeeshan Haider -----------------------------------------...

Outlook 2000
When I attempt to open OUTLOOK from the desk top icon, I receive an error message stating: "outlook.exe has generated errors. Does anyone have a correction for this problem or solution. PS - I also receive this error when I attempt to open outlook from: "start/programs. Thanks Can you get any more details about the error? "Breck" <Npba900@aol.com> wrote in message news:022e01c3674a$b2fc35e0$a401280a@phx.gbl... > When I attempt to open OUTLOOK from the desk top icon, I > receive an error message stating: > > "outlook.exe has generated errors. ...

Error sending CRM Email through VPN Connection
Hello! We have remote users that work with CRM on-line through VPN Connection. Sales for Outlook though VPN works in general, but when someone tries to send CRM email from the Outlook and presses the Send button, he gets an "unknown error". Could you advise us? Thank you, Elena ...

Diagonal error lines in a scatter?
I have a scatter with 2 series. Series 1 plots "Original GM%" vs "Original Sales". Series 2 plots "New GM%" vs "Sales At New Price". The two series have the same number of points; in each series, a point represents a customer. So every company has two points on the chart, one in series 1 and one in series 2. Is there anyway to automatically connect corresponding points in the two series? I know I can use error bars if only the GM changes or only the sales changes (that is, if a company's point in series 2 is vertically or horizontally aligned wi...

Run-time error '424'
Can anyone advise why I am getting a run-time error with this line (and what I need to do to get it working?): Me.Parent!Form.Bookmark = rst.Bookmark 'reposition the form Here is the code I am trying to run: Sub Form_Click() Dim rs As DAO.Recordset If Me.Parent.Dirty Then Me.Parent.Dirty = False End If 'Search in the clone set. Set rs = Me.RecordsetClone rs.FindFirst "[RecID] = '" & Me.RecID & "'" If Not rs.NoMatch Then Me.P...

Help with hlookup formula
I haven't worked with Excel much, but I'm guessing what I want to do is possible. I have two sheets in a workbook. One lists about 100 serial numbers. The other lists about 5000 serial numbers with additional information on each. So what I want to do is filter and see only the matches between the two sheets. It looks like you might be able to do it with hlookup, but I can't figure out the syntax. Is this possible? Thanks so much. VLOOKUP would probably be more suitable. Assuming your sheet with the 5000 serial numbers is called Master and has data starting in row 2 with a h...

Hiding 0 values in line charts
I have two columns, one is 2004 Weekly Sales Revenue and one is 2005 Weekly Sales Revenue. Obviously, 2004 has data for all 52 weeks. 2005 only has data for the weeks-to-date. How can I chart this data on a line graph-- showing comparison between the two years--and not chart the zero values? Thanks! 1) Fill empty cell with =NA(), OR 2) Click on chart to activate; use Tools|Options->Chart and specify how empty cell are to be treated, OR c) Use dynamic chart: see the Excel Tips & Tricks page on my website best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps f...

Getting the minimum value on the x axis automatically
Hi there I am graphing times and dates with a stacked bar graph. The first value on each bar is the start date/time and the second bar is the difference from the first time. I make the first bar invisible and what I end up with is a nice little bar going from start time to finish time. The problem I have is that the start time of say midnight 1/1/08 is 39448. So my graph which is hours wide is a tiny blip at the right hand end. The x axis config only lets me set auto (which seems to be 0), or a constant that I need to type in. I just want the minimum to be the lowest value on my chart...

Custom gridline
Is there a way to have only one single gridline in the y-axis, at a specified value? All I can find is to turn off/on gridlines in the Chart Options. Running Excel 2002. Well, what I have ended up doing is to simply insert an "Autoshape" line, after turning off the gridlines. "Barry Guidry" <bg3075@rocketmail.com> wrote in message news:%23Ag3k8t9HHA.5404@TK2MSFTNGP02.phx.gbl... > Is there a way to have only one single gridline in the y-axis, at a > specified value? > > All I can find is to turn off/on gridlines in the Chart Options. > > Run...