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" 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.  |