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 » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Excel LOOKUP Question



 
 
Thread Tools Display Modes
  #1  
Old December 15th, 2008, 09:05 PM posted to microsoft.public.excel.links
ritpg
external usenet poster
 
Posts: 5
Default Excel LOOKUP Question

I have a need to do what I believe is called a two dimensional
search. I want to populate a cell with the contents of a cell that
exists at the intersection of a row and column each of which contains
a known value.

I've come to the conclusion that a combination of the HLOOKUP and
MATCH functions should do it. However, I don't seem to be able to get
it to work.

Here are the real Excel sheets and matching cell equations so you have
all the info. I am trying to populate column B of Sheet 1 with the
values located in Sheet 2 at the intersection whose row contains the
person's name and whose column contains the date in cell B1 of Sheet
1.

Sheet 1 with incorrect results from equations (shown below) in columns
B and C:

A B C
1 Date: 11-Apr-09
2 Name: Cum Hrs From Row No.
3 Joe 0 11
4 Sue #REF! 14
5 Mike 70 12
6 Mary 0 11

Sheet 2:

1 4-Apr-09 11-Apr-09 18-Apr-09 25-Apr-09
2 Mary
3 Period Hrs. 80 80 70 60
4 Cum Hrs 80 160 230 290
5 Joe
6 Period Hrs. 75 75 60 80
7 Cum Hrs 75 150 210 290
8 Sue
9 Period Hrs. 60 60 70 80
10 Cum Hrs 60 120 190 270
11 Mike
12 Period Hrs. 70 70 60
80
13 Cum Hrs 70 140 200 280

The following is the equation I have come up with so far for Sheet 1
cells B3:B6

=HLOOKUP($B$1,'Sheet 2'!$A$1:$E$13,MATCH(A3,'Sheet 2'!$A$2:$A
$13)+2).

I added 2 rows at the end to get the 2nd row following the row
containing the person's name.

I also broke out the equation

=MATCH(A3,'Sheet 2'!$A$2:$A$13)+2

and put it in cells C3:C6 to see what row the MATCH function is
returning.

As you can see, the MATCH function is not returning a correct value
and I have no idea why. When I
substitute the correct row number for the MATCH function in the
HLOOKUP function, I get the correct results. So I know the HLOOKUP
function is working correctly.

Anybody have any thoughts? Please?

Thanks.
Terry
  #2  
Old December 15th, 2008, 09:38 PM posted to microsoft.public.excel.links
Lars-Åke Aspelin[_2_]
external usenet poster
 
Posts: 722
Default Excel LOOKUP Question

On Mon, 15 Dec 2008 12:05:25 -0800 (PST), ritpg
wrote:

I have a need to do what I believe is called a two dimensional
search. I want to populate a cell with the contents of a cell that
exists at the intersection of a row and column each of which contains
a known value.

I've come to the conclusion that a combination of the HLOOKUP and
MATCH functions should do it. However, I don't seem to be able to get
it to work.

Here are the real Excel sheets and matching cell equations so you have
all the info. I am trying to populate column B of Sheet 1 with the
values located in Sheet 2 at the intersection whose row contains the
person's name and whose column contains the date in cell B1 of Sheet
1.

Sheet 1 with incorrect results from equations (shown below) in columns
B and C:

A B C
1 Date: 11-Apr-09
2 Name: Cum Hrs From Row No.
3 Joe 0 11
4 Sue #REF! 14
5 Mike 70 12
6 Mary 0 11

Sheet 2:

1 4-Apr-09 11-Apr-09 18-Apr-09 25-Apr-09
2 Mary
3 Period Hrs. 80 80 70 60
4 Cum Hrs 80 160 230 290
5 Joe
6 Period Hrs. 75 75 60 80
7 Cum Hrs 75 150 210 290
8 Sue
9 Period Hrs. 60 60 70 80
10 Cum Hrs 60 120 190 270
11 Mike
12 Period Hrs. 70 70 60
80
13 Cum Hrs 70 140 200 280

The following is the equation I have come up with so far for Sheet 1
cells B3:B6

=HLOOKUP($B$1,'Sheet 2'!$A$1:$E$13,MATCH(A3,'Sheet 2'!$A$2:$A
$13)+2).

I added 2 rows at the end to get the 2nd row following the row
containing the person's name.

I also broke out the equation

=MATCH(A3,'Sheet 2'!$A$2:$A$13)+2

and put it in cells C3:C6 to see what row the MATCH function is
returning.

As you can see, the MATCH function is not returning a correct value
and I have no idea why. When I
substitute the correct row number for the MATCH function in the
HLOOKUP function, I get the correct results. So I know the HLOOKUP
function is working correctly.

Anybody have any thoughts? Please?

Thanks.
Terry


Have a look a the MATCH function in help.
You will require a third parameter , Match_type, to the MATCH
function.
Set this parameter to 0 (zero) as your data in the $A$2:$A$13 range is
not sorted.

Hope this helps / Lars-Åke
 




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 07:07 PM.


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