Home |
Search |
Today's Posts |
#1
|
|||
|
|||
look up more than 1 field
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
=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 -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table field types | Excel Discussion (Misc queries) | |||
Stop text from stringing into next field when empty | Excel Worksheet Functions | |||
Stop text from stringing into next field when empty | Excel Worksheet Functions | |||
Drop time in date/time field | Excel Worksheet Functions | |||
Problems with Pivot Table Field Sorting in Excel 2002 | Excel Discussion (Misc queries) |