• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

True but don't match.

G

gina clemens

New Member
Hi,


I am using the formula


=VLOOKUP(H128,'First Conversion'!$1:$1048576,MATCH(G126,'First Conversion'!$1:$1,0),FALSE)


The cell H128 that I am looking up is a date and time which has been created using a formula. When I use the vlookup above it works fine for the first two dates and times but then when I get to the date and time in H128 it can not find it in the lookup array.


I have tryed =H128='First Conversion'!A4 ('first conversion A4' being the cell it should be finding) and it =TRUE


But when I use =MATCH(H128,'First Conversion'!A2:A4,0) it does not find cell A4 as a match.


If I copy and paste value 'first conversion A4' into H126 the vlookup works correctly.


Why is my vlookup not working!?
 
this is just a guess, but if you look at the cell contents of H128, your probably going to see your formula and vlookup is not finding a match in that formula, you may want to convert the contents of H128 to the value of the formula...


Hope this can help
 
Thanks

Sorry for my slight confusion but do you mean copy the formula and paste as a value? I tryed copying the formula in H128 and pasting as a value, no luck, so also did the same with the cell that it should be looking up in the array and still no luck. But if i select the cell it should be finding in the array and copy pasting value in to H128 then the vlookup does work.

I also tryed converting the date/time formulas it is looking at and for into number format but again no luck.
 
Hi Gina ,


Please understand that dates on their own are integers ; dates and times are decimal numbers.


Can you change the format for the column which has the dates and times , as also the format of H128 , to Number with 30 decimal places , and see whether the value in H128 is exactly the same as the value in 'First Conversion'!A4.


Excel stores numbers to 15 digits , but this is only if the integer part is 0 ; if you enter a number such as 0.24234553424916162344 , which has more than 15 digits of precision , Excel will store only 0.242345534249161 , removing all the digits after the 15th.


Now , if the number also has an integer part , the situation worsens ; enter a number such as 317.123423545234532584 , and excel will store only 317.123423545234 , with only 12 digits after the decimal , since the integer part has 3 digits.


Thus , if you have a date and time such as 3/20/2013 9:19:45 AM , what Excel sees is a number such as 41353.3887152778 , with only 10 decimal digits. Now , if you are getting a date and time through a formula , the formula may or may not be arriving at the exact figure that is stored as a result of user entry.


Thus , for example , suppose you take the same value 41353.3887152778 , and change it in the last digit , to get 41353.3887152779 , when this value is formatted as a date and time , you will still see 3/20/2013 9:19:45 AM.


Narayan
 
Thanks Narayan.

I have just had a go at what you suggested and get a value to 10 decimal places but it is still excally the same number for both the cell it is looking up and the one it should be finding.

Thanks though - any other suggestions?
 
Hi Gina ,


I copied your formula


=VLOOKUP(H128,'First Conversion'!$1:$1048576,MATCH(G126,'First Conversion'!$1:$1,0),FALSE)


and pasted it in a blank worksheet , and then populated the relevant cells with data ; I got an error.


When I changed the reference within the MATCH function to $A1:$F1 , I got 0 as the result.


When I populated the proper column in the 'First Conversion' sheet with data , I got the correct result.


I can only imagine that the MATCH function is looking at different columns for the different formulae ; if you could upload your workbook , it would be a matter of a few minutes !


Narayan
 
Hi Narayan,

Sorry about the delayed response, I had to approach my problem differently as I couldn't get the formula to work and it skipped my mind but I have just encountered the same problem in another file.

I will upload the new file I am having the issue with as it is far smaller and doesn't contain any sensitive situation. Same deal though where my vlookup works for the first few cells but then stops. The equals function says "TRUE" so the cells are the same, but the match function has an error so cannot identify the to cells as the same. This vlookup doesn't contain the match function as my last one does but I think it is the same underlying issue.

Please ignore the hidden cells in this file as I haven't done my best work in there!


Thanks,

Gina.
 
Hi Gina ,


This forum does not have a provision for either uploading files or attaching files to posts.


What you can do is to use your preferred file-sharing website ( RapidShare , Hotfile , DropBox , SkyDrive , GoogleDocs , SpeedyShare ... ) to upload your file , give others permission to access and download the file , and then post that access link here , in this same topic.


Narayan
 
Reading the Green Sticky posts

http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Gina


You have a very small rounding error here:


If in a blank cell you put

=(L4-H21)=0

Excel displays false


where:

=ROUND((L4-H21),0)=0

Excel displays true


in fact

=ROUND((L4-H21),10)=0 = True

=ROUND((L4-H21),11)=0 = False


So you will need to use this in your formulas


This error occurs as in Column L you have formulas like:

=L2+TIME(0,15,0)

Time(0,15,0) is adding 15 minutes to the previous time

15 Minutes is the same as 15/(24*60) = 0.01041666666

and it is this division that is causing small remnants of numbers to remain


In P4 you can use:

=MATCH(ROUND(L4,4),ROUND(B21:I21,4),0) Ctrl+Shift+Enter


In M2 use:

=INDEX($I$2:$I$2002,MATCH(ROUND(L2,4),ROUND($H$2:$H$2002,4),0)) Ctrl+Shift+Enter

Then Copy M2 to M3:M2002
 
You must log in or register to reply here.
Back
Top