look up more than 1 field

[mango]

dear all,
how to use lookup function to look into 'table' by checking if month and
currency can match then i will take the rate out from 'table' and put in
'list'.
if cannot any suggestion? vlookup only allows 1 parameter. but now i need to
match more than 1 field. how?
please help.


list
mth cur amt curr
Jan Pound 200 2.3 460
Feb USD 450 1.3 585


table
mth curr rate
Jan USD 1.2
Jan Pound 2.3
Jan HKD 1.1
Feb USD 1.3
Feb Pound 2.1
Feb HKD 1.0

[mangesh_yadav]

Lets say your table is in the range A10:C15 (the table which you have given below). And lets say the two example (list) is in the first 2 rows starting A1.

To get the rate, use the following formula:
=SUMPRODUCT(--($A$10:$A$15=A1),--($B$10:$B$15=B1),$C$10:$C$15)
and copy down.

where A1 has the month, B1 the currency

Mangesh

[CLR]

Add a new column on the left edge of your table and therein CONCATENATE the
month and currency into that one column.......then use it to lookup the
"concatenated" month and currency you are questioning........this way you
can "look up two things at once", so to speak.

Vaya con Dios,
Chuck, CABGx3


"mango" <[email protected]> wrote in message
news:[email protected]...
> dear all,
> how to use lookup function to look into 'table' by checking if month and
> currency can match then i will take the rate out from 'table' and put in
> 'list'.
> if cannot any suggestion? vlookup only allows 1 parameter. but now i need
to
> match more than 1 field. how?
> please help.
>
>
> list
> mth cur amt curr
> Jan Pound 200 2.3 460
> Feb USD 450 1.3 585
>
>
> table
> mth curr rate
> Jan USD 1.2
> Jan Pound 2.3
> Jan HKD 1.1
> Feb USD 1.3
> Feb Pound 2.1
> Feb HKD 1.0
>

[mango]

Dear Mangesh,
Thanks for yr help. it works.
may i know how this sumproduct uses for? i do not understand. what about the
match and index function?
rgds


"mangesh_yadav" wrote:

>
> Lets say your table is in the range A10:C15 (the table which you have
> given below). And lets say the two example (list) is in the first 2
> rows starting A1.
>
> To get the rate, use the following formula:
> =SUMPRODUCT(--($A$10:$A$15=A1),--($B$10:$B$15=B1),$C$10:$C$15)
> and copy down.
>
> where A1 has the month, B1 the currency
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=378013
>
>

[mangesh_yadav]

=SUMPRODUCT(--($A$10:$A$15=A1),--($B$10:$B$15=B1),$C$10:$C$15)

The first part
$A$10:$A$15=A1
checks all the cells in the column, and when equal returns something like
False, True, False, False, False
The -- preceding it converts this to
0,1,0,0,0
Same with the second column (B10:B15), The last column has actual values. The sumproduct simply multiplies each element row-wise and then adds up. So we have
0,1,0,0,0 * 0,1,0,0,0 * 11,12,13,14,15
=0,12,0,0,0
=12

You can select the cell which holds the formula and click on 'Evaluate formula' to see how it works.

Mangesh

Mangesh