Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
How can you get by using V or H lookup to copy data from 2 cells on one sheet into 1 cell in another cell separated with a hyphen.
This is my current code which works, but I want the data from the next cell to also be shown in the same cell. I'm also working in different spreadsheets, hence the reference of the file name.
=HLOOKUP([test1.xls]Sheet1!$H$10,[test1.xls]Sheet1!$A$10:$Z$10,1,FALSE)
H10 = 25/8/09
H11 = 1/9/09End result = 25/8/09 - 1/9/09
Thanks Ian
Hi,
Just use your two lookup formulas separated by & " - " &
something like this:
=HLOOKUP() & " - " & HLOOKUP()Regards
1 
I left the file names and sheet names out for ease of testing and displaying, but you'll get the idea.
This should be all on one line.
=HLOOKUP($H$10,$A$10:$Z$10,1,0) & " - " &
INDEX($A$10:$Z$10,1,MATCH(HLOOKUP($H$10,$A$10:$Z$10,1,0),$A$10:$Z$10,0)+1)- You know what the first HLOOKUP will do.
- The & " - " & will give you your "space hyphen space"
- The MATCH will find the position in the array A10:Z10 of the value returned by the HLOOKUP and adding 1 to that position will give you the position of the next cell over.
- The INDEX will return the value that's in the cell determined by the position determined by MATCH(HLOOKUP...) + 1
0
Humar,
That's where I went at first (and almost posted it!) but then I realized that the OP is looking up values across different sheets in different files. I also think the example cells he used made the question a bit confusing.
This is my assumption:
In the sheet with the formula (e.g. [File1]Sheet1) he has 25/8/09 in H10 and he is looking that up in A10:Z10 of another file (e.g. [File2]Sheet1). He doesn't have anything in H11 of [File1]Sheet1 to look up, so he can't use 2 HLOOKUPs. He is trying to retrieve the value from [File2]Sheet1!H11 without actually looking it up.
I'm assuming it just a coincidence that both sheets have 25/8/09 in H10, because if the lookup_value was always in the same cell in both sheets, he wouldn't need HLOOKUP at all.
Does that makes sense? I'll admit that this explanation confuses me and I wrote it!
0
also note if you're looking up dates and using the "&" separator, you need to use
TEXT(value that returns a date, "d/m/yy")
Put this around your lookup/index in derbydad's solution above and it should be fine
0
Good point - but make sure you use 2 TEXT functions, one around both "halves" of the formula.
Just one around the whole thing won't work.
0
Hi DerbyDad03,
Hmm,
My assumption was that H10 and H11 were cells holding the results of two Hlookups.
The example result is "25/8/09 - 1/9/09", i.e., H10 & " - " & H11.
....
anyway we hope to hear back from Ian.
As to TEXT(), yes, we need to include it around each lookup formula when the results are dates (and presumably any result where the returned text is not the same as the underlying value, such as currency )
Regards
0
Wow, thanks for the great responses....
Sorry I made a mistake in my example and it should have been
H10 = 25/8/09
I10 = 1/9/09End result = 25/8/09 - 1/9/09
Anyhow Humar your first reply works wonders, in my testing before posting the Q. I'd just not got the 2nd hlookup in the formular.
To clear up I'd be getting the data from the same file & sheet.
So thanks everyone for your help, cheers Ian
0
Ok now I'm having problems, probably with the text command I have this:
=HLOOKUP('C:\My Documents\file\Clients\[test1.xls]Sheet1'!$C$10,'C:\My Documents\file\Clients\[test1.xls]Sheet1'!$A$10:$Z$10,1,FALSE) &" - "& HLOOKUP('C:\My Documents\file\Clients\[test1.xls]Sheet1'!$H$10,'C:\My Documents\file\Clients\[test1.xls]Sheet1'!$A$10:$Z$10,1,FALSE)
Cell c10 has 14/07/2009
Cell h10 has 15/07/2009Yet the end result is 40008 - 40009
C = arrival date
H = departure dateThanks Ian
0
Hi,
The output you got was because Excel stores dates as numbers. The 40008 and 40009 numbers are the 14th and 15th of July 2009. Excel calculates dates based on 1 = 01 January 1900, and adds 1 for every subsequent day. (Apart from the error of assuming that 1900 was a leap year, which it wasn't, 60 returns 29 Feb 1900, a date that didn't exist!).
To get your formula to display dates you need to use the TEXT formatting command mentioned by jon k.
Your formula becomes:
TEXT(HLOOKUP(
'C:\My Documents\file\Clients\[test1.xls]Sheet1'!$C$10,
'C:\My Documents\file\Clients\[test1.xls]Sheet1'!$A$10:$Z$10
,1,FALSE)
,"dd/mm/yy")
&" - "&
TEXT(HLOOKUP(
'C:\My Documents\file\Clients\[test1.xls]Sheet1'!$H$10,
'C:\My Documents\file\Clients\[test1.xls]Sheet1'!$A$10:$Z$10
,1,FALSE)
,"dd/mm/yy")Changing the "dd/mm/yy" will change how the dates display.
Regards
0
Thanks Humar, was on the verge of starting to do a access database, as I know extracting data is easier. Your code works wonders and I've changes the dd/mm/yy to [$-809]dd mmmm yyyy in order to show the full date.
Gracias a nuevo (Thanks again)
Ian
0
Ian,
Is the formula you posted in Response # 8 related to the one you originally posted or is it a different (but similar) question?
In your OP you said "I want the data from the next cell to also be shown in the same cell" and later posted an updated example using H10 & I10. In other words H10 and the next cell.
In Response # 8, your formula is using HLOOKUP to look up values in 2 non-contiguous cells, C10 & H10.
Is the next cell question no longer an issue or by next cell did you mean another cell?
In addition, all of the formulae you posted are looking up values in the same file and same sheet as the formulae reside in. Can we assume that these are just examples and that you really have 2 different files? If not, I'm not sure why you would use HLOOKUP to look up in a cell that resides in the lookup_array on the same sheet.
In other words, looking up C10 and H10 in A10:Z10 of the same sheet will simply return the values in C10 and H10, so you could just use =H10 & " - " & C10.
Please clear up these 2 points of confusion for me.
Thanks!
0
Everything was based on a 2 test files, the original files will explain this better.
In one excel document called (reservations) I have a list of reservations going across the screen i.e. a2,b2,c2,d2
a2 arrival date - b2 arrival time, c2 departure date etc...
In another excel document (invoice) which would take the info from the excel document reservations and populate so I don't have to re-type everything again. In one of the fields on the invoice I have a cell accommodation dates and needed both A2 and C2 separated by the hyphen.
On my test documents it all worked fine, however when I tried to start using hlookup or vlookup on the invoice it wouldn't work. And I thought oh what the f***, I played around for a few hours with no success and then just decided to link the invoice cell to the correct cell on the reservation cell as follows:
='C:\My Documents\Clients\[Reservations2010.xls]Sheet1'!$AC$2
Thanks for your help.
Ian
0  |
 |
 |
| Login or Register to Reply | |
| Login | Register |
| Ads by Google |
