vlookup doesn't work consistently (I think), kindly help



  • 1. Format Cells to calculate hours and minutes taken to complete
    Hi Richard Grateful if you can tell me where in my formula I can make the changes you suggest because if I put /24 at the end vice +24 and format cell to [h]:mm then it returns 4:00 Any help would be greatly appreciated. Thanks Andy "RichardSchollar" wrote: > > > Well, you could modify your formula so that the final result is > divided by 24 (ie putting it back into Excel time) and then format the > formula cell as an Excel time format ie > > [h]:mm > > (if tasks may take over 24 hours) > > Alternatively, you could multiply by 60 in which case you will end up > with an integer value representing the number of minutes to complete > the task. > > Hope this helps! > > Richard > >
  • 2. Format Cells to calculate hours and minutes taken to complete task
    Hi I have the following calculation in my column E: =((D3-C3)*24)-(NETWORKDAYS(C3,D3)*24)+24 Column D is Date/Time order completed in format: dd/mm/yyyy hh:mm:ss Column C is Date/Time order received in format: dd/mm/yyyy hh:mm:ss Currently my results in column E return 0.17 which equates to 10 minutes - I really want it to show 10 minutes and not 0.17 Can anyone help?
  • 3. Consolidation of data from cell in active sheet of closed workbook
    Your expert advice would be most appreciated: Using Excel 2003, I want to sum the contents of cell A1 on the last active worksheet of 22 other workbooks into cell A1 of a master consolidation workbook. The active sheet in the 22 workbooks will change from week to week, so I need the master workbook to only retrieve data from cell A1 in the sheet that was visible when the other 22 workbooks were last saved. I can put a formula in the master consolidatioin workbook to link to a named worksheet in the 22 other workbooks but cannot get it to link to the cell in the last active worksheet. I hope this makes sense, and that you can assist. Regards
  • 4. SQL syntax
    I will be very grateful for the SQL syntax to use for an ADO query on a large csv to pull in all the rows of data for one specific fund to an Excel spreadsheet. There are about 35 funds listed in the first column headed "Funds". I can get all the data across but i would like to just import the data for one fund say fund "XYZ" in the "Funds" field. -- with kind regards Spike

vlookup doesn't work consistently (I think), kindly help

Postby UHJhZGhhbg » Sat, 18 Oct 2008 01:55:01 GMT

I am using VLOOKUP() to pull out a column value from a range of data. Here is 
the problem: 
When I use this fn on the same page as the referenced range, it works fine. 
For example, 
=VLOOKUP(L2,A2:I18,5) looks up the value in L2, finds it in the first column 
and returns the 5th column data... BUT

=VLOOKUP(G6,'Master Prep List'!A2:I18,5) where G6 represents a value found 
in the same range on a different worksheet, here Master Prep List,  returns 

This does not seem correct to me, unless I am not using the fn correctly, 
which is always a possibility. I am using Excel 2007,

Any help/insight would be appreciated. 

Re: vlookup doesn't work consistently (I think), kindly help

Postby Niek Otten » Sat, 18 Oct 2008 02:10:43 GMT

If the second formula is on row 6, it is probably the result of a Copy and 
Paste action. Are you sure that in this formula the range 'Master Prep 
List'!A2:I18 is addressed and not 'Master Prep List'!A6:I22 ?
It is always safer to make the addresses of table absolue (like $A$2:$I$18) 
or, even better, use a defined name.

Another possibility is that there are spaces in either the search argument 
or the table. Or, if one of them was copied from a website or imported from 
another program, other invisible characters.

Anyway, in your case, with the 4th argument omitted, #NA suggests that the 
search argument is smaller than the first item from the table.

Kind regards,

Niek Otten
Microsoft MVP - Excel

Re: vlookup doesn't work consistently (I think), kindly help

Postby U2VhbiBUaW1tb25z » Sat, 18 Oct 2008 02:48:01 GMT

If I may add, another possibility is that the Master Prep List sheet has an 
#N/A in one of the cells. If so, VLOOKUP will always return #N/A. Remove or 
correct your #N/A from MAster Prep List, and it will work.

Re: vlookup doesn't work consistently (I think), kindly help

Postby UHJhZGhhbg » Sat, 18 Oct 2008 03:06:00 GMT

Thank you all for your input. I am quite certain the formulas are correct.I 
believe the problem has to do with the fact the 2nd worksheet on which  I am 
using (requiring the referenced range in VLOOKUP) has a pivot table on it. 
There is something about this in the help menu.

Even though I am trying to enter the formula in a field far removed from the 
Pivot table, I get this error anywhere on that worksheet. Is there some way 
to limit what I might call "the effectual" range of the Pivot tabe?

Thanks again,

RE: vlookup doesn't work consistently (I think), kindly help

Postby fkw » Sat, 18 Oct 2008 03:12:05 GMT

Is the value in G6 in the first column of the range 'Master Prep 
List'!A2:I18?  If not, adjust the range so it is.

Re: vlookup doesn't work consistently (I think), kindly help

Postby U2VhbiBUaW1tb25z » Sat, 18 Oct 2008 04:06:31 GMT

that shouldn't be it. I use VLOOKUP against Pivot Tables on a daily basis.

And you are absolutely sure the value in G6 does appear in column A of 
Master Prep List? If you copy the cell and perform a Find, do you locate the 

Similar Threads:

1.why doesn't SendKeys work consistently?

In my macro, I need to use the SendKeys command for various reasons.  But 
it's wierd: sometimes SendKeys works like I programmed them, sometimes just 
some of the SendKeys commands work, sometimes Excel "calls" the SendKeys in a 
different order than from what I've programmed, and sometimes SendKeys 
doesn't work at all.   All of the above can happen when
1. I run the macro using a keyboard command or
2. if I run the macro by merely clicking "Run" on the macro list form or
3. if I do an F8 within Visual Basic.  (Actually, it seems like SendKeys 
never works when I do the F8 thing.)

For example, here's a simple Macro that just won't run consistently/correctly:

SendKeys "%(=)"     'inputs an AutoSum  (Alt-equals)
SendKeys "{ENTER}"
ActiveCell.Offset(-1, 1).Range("A1").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Offset(0, -1).Range("A1").Select
SendKeys "{END}"
SendKeys "{UP}"
SendKeys "{UP}"

I can't get a handle on it.  Obviously, I'm doing something wrong.  Any 


2.Autocorrect doesn't consistently work

A user of mine uses Word as email editor in Outlook 2003. Sometimes the 
autocorrect works to correct words as he types while other times, it doesn't 
autocorrect, even though it does underline it as misspelled. What could cause 
this variance if the settings have NOT been adjusted in between uses?

3.vlookup does not work consistently

sometimes it returns #N/A for no reason. 

e.g. see this formula 
=IF(COUNTIF('competitor guideline 
rates'!$C$16:$C$29,C12)=0,0,VLOOKUP(C12,'competitor guideline 

If you read it carefully, there is no way #N/A could be returned.  In this 
case, COUNTIF finds the argument in C12 in the array, but VLOOKUP does not!

4.Vlookup not working consistently

I've searched all through other posts and have not found anyone with a 
similar problem.  We have several workbooks that have been set up for at 
least 3 years or more.  Some of them suddenly, on any given line, will not 
return the correct data even though it should be.  At first it started with 
just a line or two, and then slowly began to increase.  At first we thought 
the file was corrupt, so we re-created it.  That didn't help.  Now it has 
started doing it in one of our other files--works fine, and then all of a 
sudden starts returning zeros. 

The formula I happen to be using right now is:

Personally, I don't think it's a problem with the formula because the 
formulas very from workbook to workbook.  It can work fine on line 1 to 25, 
quit working for 26 to 30, and then start working again on line 31. It's 

Anyone have any ideas?

5.Error trapping for VLOOKUP...doesn't work so help!

I want to trap any error that may occur using a VLOOKUP.
I tried this in a cell:
=if(iserror(VLOOKUP(K1363,LOOK,6,FALSE)),"No lookup value error", VLOOKUP(K1363,LOOK,6,FALSE))
but this won't work because the value_if_false part of the IF() statement corrupts (for lack of a know word) the IF() statement.  That is, if there actually is
an error with the VLOOKUP, the ISERROR will evaluate to TRUE but the VLOOKUP will also dump its error into the IF() statement and that error keeps the IF()
statement from operating.

Suggestions on trapping VLOOKUP errors and "fixing" them like I'm trying to do with the IF() statement?  The IF() statement isn't necessary, it's just what I
though would logically work...
   Toby Erkson
   Oregon, USA

6. Why doesn't status bar update consistently?

7. Load-on-demand COM add-in for Word doesn't unload consistently

8. Load-on-demand COM add-in for Word doesn't unload consistently, co

Return to MS EXCEL


Who is online

Users browsing this forum: No registered users and 63 guest