Combine VLOOKUP with TEXT Conversion?

Hi,

Im performing a VLOOKUP to return a decimal value:

=VLOOKUP(A15,wk_2,17,FALSE)

I then want to combine that with something like

=TEXT(A15/(24*60),"[h]""hr ""m""m""")

so that it converts the VLOOKUP number into hours and mins, whilst annotating accordingly. Im a bit stumped, could anyone help?

Thanks

There are instructions at the top of the page explaining how to attach your sample workbook.

Don't convert it to etext. Apply a cistom format, e.g.:

hh" hr "mm" min"

Edit: Hahaha. I'm sitting in near darkness. etext = text, cistom = custom

Originally Posted by Pete_UK

If you want to combine them into one formula, it would be this:

=TEXT(VLOOKUP(A15,wk_2,17,FALSE)/(24*60),"[h]""hr ""m""m""")

Hi,

Thanks, that's spot on. I nearly had it, I put my vlookup before my text.....Sorry I didnt include a sample workbook I didnt think it would be necessary

It wasn't in the event, but it's always a good idea to add one, as sometimes it avoids unexpected bumps in the road.

I just hope you don't want to add them up somewhere else on your sheet!!

Fair enough, Pete. But the cistom (!!) formatting is a better way forward, IMHO... I rarely disagree with you, sir, but on this occasion... I will. I spent ages trying to sum +ve and -ve time tonight, which made me realise the problems of time as text. Nightmare.

Either way, it seems that Elvis has left the building...

For what it's worth, I would not use VLOOKUP when the lookup column and the results column are 17 columns apart - I'd use INDEX MATCH instead.

Originally Posted by AliGW

For what it's worth, I would not use VLOOKUP when the lookup column and the results column are 17 columns apart - I'd use INDEX MATCH instead.

Why? Apart from the problem of being able to count to 17 without error.....

Well, that's one very good reason right there!!!