Hlookup with Merged cell as look up value

[srs]

Hopefully someone can help me out on this one. I have a spreadsheet that
looks similar to this:

2/15/2010 3/15/2010 . . .
Site# INV VALUE INV VALUE
1 34 332 40 440
2 47 250 47 250
3 55 476 36 420
Total 136 1058 123 1100

I am trying to use a Hlookup to return both the Totals for both the INV and
Value in a compainon sheet. The date cells are merged over the INV and VAL
Colums so my Hlookup formula only returns the INV value. Anyone know of a
way to have a Hlookup return the value in the right column or another work
around for this problem.

[T. Valko]

Kind of hard to figure out where your stuff is so you'll have to go by my
sample...

This is in the range B6:E6

>136,1058,123,1100

This in the merged cells B1:C1

2/15/2010

This in the merged cells D1:E1

3/15/2010

A10 = 2/15/2010
B10 = INV
C10 = VALUE

Enter this formula in B11:

=INDEX(B6:E6,MATCH(A10,B1:E1,0))

Enter this formula in C11:

=INDEX(B6:E6,MATCH(A10,B1:E1,0)+1)

--
Biff
Microsoft Excel MVP

"srs" <s...@discussions.microsoft.com> wrote in message
news:0D1837F9-4306-4FDB...@microsoft.com...