look up more than 1 field

[Guest]

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

 

[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

 

[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

 

[Guest]

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]

=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