Combing a V Lookup result with ("00"&A2)*1 in same formula

[jtfranco]

Please excuse the long subject title, I coulnd't word the topic an
other way.

I'd like to combine these two formulas

1. G2=VLOOKUP(L2,'Avaya Data'!$B:$O,3,FALSE)
2. Z2=("00"&G2)*1

Im importing call data in General format, which comes in such a
".80138" and sometimes it comes in ":18".
You can change ".80138" to custom hh:mm:ss which changes it to 19:14:0
but it doesn't change the ":18" to a uniform 00:00:00 format.

So to get cell G2 in correct format I have been doing the following:

Z2=("00"&G2)*1
Format Change: Custom> hh:mm:ss
Copy
Paste Special, number and format into cell G2

Unfortunately its hard to automate this process as the rows of data ar
constantly changing with new associates. So basically what Im asking i
how I can combine Formula 1 with Formula 2 in one formula.

Thank you for your time

 

[Claus Busch]

Hi,

Am Fri, 29 Mar 2013 23:15:10 +0000 schrieb jtfranco:

Please excuse the long subject title, I coulnd't word the topic any
other way.

I'd like to combine these two formulas

1. G2=VLOOKUP(L2,'Avaya Data'!$B:$O,3,FALSE)
2. Z2=("00"&G2)*1

Im importing call data in General format, which comes in such as
".80138" and sometimes it comes in ":18".
You can change ".80138" to custom hh:mm:ss which changes it to 19:14:00
but it doesn't change the ":18" to a uniform 00:00:00 format.

try in Z2:
=IF(ISNUMBER(FIND(":",G2)),("00"&G2)*1,G2)
or without helper column G:
=IF(ISNUMBER(FIND(":",VLOOKUP(L2,'Avaya Data'!$B$1:$N$100,3,0))),("00"&VLOOKUP(L2,'Avaya Data'!$B$1:$N$100,3,0))*1,VLOOKUP(L2,'Avaya Data'!$B$1:$N$100,3,0))



Regards
Claus Busch

 

[jtfranco]

Sorry for the delay I had posted this on a Friday and just got in o
Monday. Let me give it a shot. Thank you

 

[jtfranco]

P108 works


P109 Does Not Work


As you can see.
P109 does not work nor do the following 59 rows up to P158.
Consequently P109-V109 do not work all the way down to P158-V158 do no
work and come up #N/A

Can anyone help? Is the problem is that there is a limit to how long th
VLookup can be? can this be fixed?

Attatched are pics of the P108 cell and P109 cell and their formulas

+-------------------------------------------------------------------
|Filename: p108.jpg
|Download: http://www.excelbanter.com/attachment.php?attachmentid=832
|Filename: p109.jpg
|Download: http://www.excelbanter.com/attachment.php?attachmentid=833
+-------------------------------------------------------------------

 

[jtfranco]

We made the range longer and it worked

Claus: Try (The range is longer – it now goes to row 1000):
=IFERROR(IF(ISNUMBER(FIND(":",VLOOKUP(N2,'Avay
Data'!$B$1:$N$1000,3,0))),("00"&VLOOKUP(N2,'Avay
Data'!$B$1:$N$1000,3,0))*1,VLOOKUP(N2,'Avay
Data'!$B$1:$N$1000,3,0)),0)

Thanks Claus u da man. Case solved. Now if we could just find out wh
shot Biggie and Tupac :-

+-------------------------------------------------------------------
+-------------------------------------------------------------------