A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Hlookup with Merged cell as look up value



 
 
Thread Tools Display Modes
  #1  
Old March 18th, 2010, 09:59 PM posted to microsoft.public.excel.worksheet.functions
SRS
external usenet poster
 
Posts: 20
Default 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.
Ads
  #2  
Old March 18th, 2010, 10:51 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Hlookup with Merged cell as look up value

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.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:32 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.