Prev: Conditional Formatting with 3 Criteria Options
Next: Use CountIf for a literal string
From: srs on 18 Mar 2010 16:59
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.
From: T. Valko on 18 Mar 2010 17:51
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" <srs(a)discussions.microsoft.com> wrote in message
news:0D1837F9-4306-4FDB-A1AB-08F670BFBD1B(a)microsoft.com...
> 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.


 | 
Pages: 1
Prev: Conditional Formatting with 3 Criteria Options
Next: Use CountIf for a literal string