How to set One value from Multiple Lookup Value

[Shibly]

Ref to the following Instance i.e in Sheet1
A B C
Destination Weight Rate
1 Chicago 500 1
2 Chicago 1000 2
3 Chicago 1500 3

I want to set up lookup value in Sheet2

A B C
Destination Weight Rate
1 Chicago 500
2 Chicago 1000
3 Chicago 1500

I want to set formula in Sheet2 Colum C. I will put destination and weight
in colum A and B and I want the rate will be automically inserted in colum C.

Thanks
Shibly

 

[Bob Phillips]

=INDEX(Sheet1!$C$2:$C$20,MATCH(1,(Sheet1!$A$2:$A$20=$A2)*(Sheet1!$B$2:$B$20=$B2),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

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

 

[Toppers]

Bob,
Curiosity: is there any advantage/disadvantage in using
SUMPRODUCT to do this? Not its normal usage I know but it will work.

 

[Bob Phillips]

I tend to avoid it John, in case there are multiple entries. With SP it will
just sum, and thus could be difficult to track the error down. I know the
INDEX gets just the first in these circumstances, but that is preferable to
adding (IMO).

--
HTH

Bob

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