assuming you mean the match 1 part,

a2=othersheet!$a$1:$a$10

the above will return an array of Boolean values TRUE or FALSE, an example
could look like this

{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FAL SE;FALSE}

the next array will do the same

b2=othersheet!$b$1:$b$10

now when you calculate TRUE or FALSE they will return 1 for TRUE of 0 for
FALSE
only TRUE*TRUE will return one, all other options will return FALSE
so if the second looks like


{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FA LSE;FALSE}

and when you multiply them they will return an array like


{0;0;0;0;0;0;0;1;0;0}

thus

=MATCH(1,{0;0;0;0;0;0;0;1;0;0},0)

will return 8 (the 8th value is 1)

then using index it will return the 8th row in the index range



--
Regards,

Peo Sjoblom


"fLiPMoD£" wrote in message
...
Dave,
Can you please explain the formular you have here...very interesting.

Thanks in advance

...Coming From where I'm From.

"Dave Peterson" wrote in message
...
I like this syntax:

=index(othersheet!$c$1:$c$10,
match(1,(a2=othersheet!$a$1:$a$10)*(b2=othersheet! $b$1:$b$10),0))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you
do it
correctly, excel will wrap curly brackets {} around your formula. (don't
type
them yourself.)

Roni wrote:

Let's say I have the following information in sheet1:
Serial # Asset code Location
1 A =vlookup(???)
2 B
3 C

In sheet2, I have the complete listing where I want to get the data
from:
Serial # Asset code Location
1 A XYZ
2 B ABC
3 C DEF

In sheet1, I want to have formula that can retrieve "location" if
serial # = 1 and asset code = A. Can anyone help me on this?

Thanks,
Roni

--

Dave Peterson