Using Index & Match functions to find data on separate worksheet.

[Andrew Duncan]

Hello all (newbie here, so I hope I am doing this correct)

I have been scratching my head trying to find the best way of solving a
problem I have within a complicated spreadsheet.
In summary I am trying to use two changable lookup values on a different
worksheet within my XLS file.

Looking around and doing research I believe I have found an answer to my
query here :
http://office.microsoft.com/en-us/excel/HA011549021033.aspx?pid=CL100570551033
showing the use of two new functions to me (Match & Index).

Mimicking and adapting the finalised formala to suit my application i have :

=INDEX(Gross!$A$3:$AR$241,MATCH($C8,Gross!A$2:A$240,0),MATCH(I$1,Gross!B$1:AR$1,0))

Gross being a Sheet within the same Excel file, Column A (A2 to A240) having
one of my reference criteria a simple four digit number in the current sheet
in cell C8, and row 1 (B1 to AR1) having my other criteria in date format,
in the current sheet cell I1

As it happens the formula returns the value below the value of C8 in column
A of the sheet Gross.

Do the Functions Match and Index work across different worksheets to where
the formula is ?
Is this explained clearly enough for helping me ?

Thanks in advance.

 

[Guest]

try:

=INDEX(Gross!$A$2:$AR$240,MATCH($C8,Gross!A$2:A$240,0),MATCH(I$1,Gross!B$1:AR$1,0))

You were matching on range A2 onwards but returning from A3 onwards - hence
the row will one out.

HTH

 

[Andrew Duncan]

Unfourtunately that does not give the result I am trying to achieve either :
It is also one column out (i.e. reading from A when it should be B)
Looking at the 'Gross' Worksheet I am trying to look up the data from (top
left corner only to represent the data) :

A B E G H
1 ad Jun-07 Jul-07 Aug-07 Sep-07
2 1612 77,369 51,579
3 1692 382,056
4 1817 2,064 10,322
5 1886 45,541
6 1896 4,206
7 1917 13,411
8 1929
9 1930 49,056 73,584 49,056
10 1941
11 1966 1,820 11,832
12 1982 10,284
13 1986 5,779
14 2011 7,839
15 2012 6,780


I am trying to find the value of C8 in column A (in this case it is A5 -
value 1886), and the value of I1 in Row 1 (in this case it is B1 - Value
1886) to then give the value in the table for 2,064.

Instead I receive the value 1817 (i.e. the value of one column down the
range.

ideas ?
Where do I change the formula :
=INDEX(Gross!$A$2:$AR$240,MATCH($C8,Gross!A$2:A$240,0),MATCH(I$1,Gross!B$1:AR$1,0))


Thanks

 

[Bob Phillips]

I get 1886 with that formula.

But as the values in column A are unique why do you need to do the match at
all?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Andrew Duncan]

Bob, All,

I have managed to play around with it and have the answer :
=INDEX(Gross!$A$2:$AR$240,MATCH($C8,Gross!$A$2:$A$240,0),MATCH(I$1,Gross!$A$1:$AR$1,0)))

FYI, the data from Column A is an outpu from an auto sorting varying sheet,
and hence changing fairly constantly. They are job reference numbers and as
you can see the top line is the dates. I am trying to forecast cashflow for
the future and rather than rebuilding it every few months I am trying to put
in the hard work at the front end - possibly over complicating things in
doing so - but nethertheless, have a automatic output for ever and a day !

Either way - problem solved I think and thank you all.

Andy