Re: Mathematica Equivalent of Excel VLOOKUP function
- To: mathgroup at smc.vnet.net
- Subject: [mg93110] Re: Mathematica Equivalent of Excel VLOOKUP function
- From: "ellisg at rogers.com" <ellisg at rogers.com>
- Date: Sun, 26 Oct 2008 01:30:37 -0500 (EST)
- References: <gdrq74$mlf$1@smc.vnet.net>
On Oct 24, 2:33 am, "Charles L. Snyder" <clsny... at gmail.com> wrote:
> Hi -
>
> I have two large csv files.The first is the data:
>
> mydata=Table[{{"jones", 34124324, 605, 54161, C33}, {"smith",
> 343214234, 550.92, 49505, V55}, {"andrews", 54554543, 550.92, 49505,
> G55}, {"robinson", 565654565, 550.90, 49505, K77}, {"burt", 9898966,
> 752.61, 54324, U88}}]
>
> the second file is the 'lookup table':
> mylookup=Table[{{54161, 3.2}, {54324, 16.3}, {49505, 12.3}}]
>
> MS Excel has a function called VLOOKUP - it finds a column of your
> data (eg, the 4th column in my example (54161, 49505, 49505,
> 49505,54324)), and sequentially looks up each value in the lookup
> table (which has unique values only), and then "grabs' the adjacent
> column ([key]value pairs), and adds the looked up value as a final
> column in the original data:
>
> result = Table[{{"jones", 34124324, 605, 54161, C33, 3.2}, {"smith",
> 343214234, 550.92, 49505, V55, 12.3}, {"andrews", 54554543, 550.92,
> 49505, G55,12.3}, {"robinson", 565654565, 550.90, 49505, K77,12.3},
> {"burt", 9898966, 752.61, 54324, U88,16.3}}]
>
Not elegant -- but try:
Table[Flatten[{mydata[[i]],Select[mylookup, #[[1]] == mydata[[i, 4]] &]
[[1, 2]]}], {i,Length[mydata]}]