Hlookup with Merged cell as look up value

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.

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" wrote in message
...
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.