On Mon, 15 Dec 2008 12:05:25 -0800 (PST), ritpg <ritpg[ at ]hotmail.com>
wrote:
[Quoted Text] >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
|