Lookup, Match & Index

[carrillod]

Hi to everybody!
I already recurring of you before and very glad to thank you for all your help.

This time I'm working on a sheet and I'm getting crazy trying to run a search by a combined formula, including lookup, match and index...
May be you can help me out with this, I'm very appreciated

In here is an attached example file.

I need to fill it in automatically the columns Z to AC in Page 1, with the manually input data in the Page 2.
The only data in common is the date, is possible to do what I want with this kind of formulas?

Thanks in advance!

PD: Sorry for my english

[Jonmo1]

You can do this with HLOOKUP.
It's pretty much teh same is VLOOKUP, but it searches Across instead of down.

Z2: =HLOOKUP($A2,'Page 2'!$B$2:$K$6,2,FALSE)
AA2: =HLOOKUP($A2,'Page 2'!$B$2:$K$6,3,FALSE)
AB2: =HLOOKUP($A2,'Page 2'!$B$2:$K$6,4,FALSE)
AC2: =HLOOKUP($A2,'Page 2'!$B$2:$K$6,5,FALSE)

then fill down.

[carrillod]

You can do this with HLOOKUP.
It's pretty much teh same is VLOOKUP, but it searches Across instead of down.

Z2: =HLOOKUP($A2,'Page 2'!$B$2:$K$6,2,FALSE)
AA2: =HLOOKUP($A2,'Page 2'!$B$2:$K$6,3,FALSE)
AB2: =HLOOKUP($A2,'Page 2'!$B$2:$K$6,4,FALSE)
AC2: =HLOOKUP($A2,'Page 2'!$B$2:$K$6,5,FALSE)

then fill down.

Jonmo: Nice!! works fine. Thanks!
But I got a little complex on file, check this out in the new attached version, now I have the score for Local and the Visitor and I want to reflect this data on Page 1, any idea?

[Jonmo1]

Run Away from Merged Cells....As fast as you can....

Stick with the 1 column per Date setup you had before.
Make 2 rows for the scores (1 for local, and 1 for visitor)

Then just adjust the RowIndex # in the Hlookup

[carrillod]

Run Away from Merged Cells....As fast as you can....

Stick with the 1 column per Date setup you had before.
Make 2 rows for the scores (1 for local, and 1 for visitor)

Then just adjust the RowIndex # in the Hlookup

I wish, believe me, but because the nature of the design of the real spreadsheet I can't.
In case I can't use another solution, I ask to use the hlookup for some data and input the other one data manually...

[Jonmo1]

If you insist...

AC2: =INDEX('Page 2'!$C$5:$S$5,MATCH(A2,'Page 2'!$B$2:$R$2,0))


But I must urge you to reconsider the merged cells.
They really cause more problems than they solve.

If it is just for visual effect, A very similar effect can be achived with "center accross selection"
Found in Format Cells - Alignment - Horizontal - "Center accross selection"

[carrillod]

If you insist...

AC2: =INDEX('Page 2'!$C$5:$S$5,MATCH(A2,'Page 2'!$B$2:$R$2,0))


But I must urge you to reconsider the merged cells.
They really cause more problems than they solve.

If it is just for visual effect, A very similar effect can be achived with "center accross selection"
Found in Format Cells - Alignment - Horizontal - "Center accross selection"

You're awesome!! it works really good!
Thanks for your help!