Re: vlookup like function sugestion
- To: mathgroup at smc.vnet.net
- Subject: [mg128808] Re: vlookup like function sugestion
- From: 11lara11 at gmail.com
- Date: Wed, 28 Nov 2012 03:14:14 -0500 (EST)
- Delivered-to: l-mathgroup@mail-archive0.wolfram.com
- Delivered-to: l-mathgroup@wolfram.com
- Delivered-to: mathgroup-newout@smc.vnet.net
- Delivered-to: mathgroup-newsend@smc.vnet.net
- References: <jlbnp4$44h$1@smc.vnet.net>
On Monday, April 2, 2012 10:25:08 AM UTC+2, Rodrigo Murta wrote: > When you are used to work with Excel, one big challenge in start > > working with Mathematica is to find one way to merge table like > > Vlookup does. > > I know by my own experience that it's very difficult to handle this in > > Mathematica, and I belive that a lot of Excel user get a little bit > > frustrated in see that one simple operation like that is so difficult. > > I would like to suggest to Mathematica team that the future version > > could have some builting in function to handle this situation. > > Meanwhile I use the below function that I would like to share with the > > community: > > > > vlookup[data1_, data2_, pk1_: 1, col1_: {2}, pk2_: 1] := > > Module[{look}, > > look[val_, table_, pk2l_] := Select[table, #[[pk2l]] == val &]; > > Join[#, > > Check[look[#[[pk1]], data2, pk2][[1, col1]], > > ConstantArray[Null, Length@col1]] // Quiet] & /@ data1] > > > > Use example: > > > > mydata={{"brazil",605,54161,a},{"india",550.92,49505,b},{"china", > > 550.92,49505,c},{"russia",752.61,54324,d}}; > > lookup={{54161,3.2,"rodrigo"},{5434,16.3,"murta"},{49505,12.3, > > "daniel"}}; > > > > vlookup[mydata, lookup, 3, {2, 3}] > > > > {{"brazil", 605, 54161, a, 3.2, "rodrigo"}, {"india", 550.92, 49505, > > b, 12.3, "daniel"}, {"china", 550.92, 49505, c, 12.3, > > "daniel"}, {"russia", 752.61, 54324, d, Null, Null}} > > > > vlookup[mydata, lookup, 3] > > > > {{"brazil", 605, 54161, a, 3.2}, {"india", 550.92, 49505, b, > > 12.3}, {"china", 550.92, 49505, c, 12.3}, {"russia", 752.61, 54324, > > d, Null}} > > > > > > Best Regards > > Murta > > PS: there is an topic about this in 2008 > > http://groups.google.com/group/comp.soft-sys.math.mathematica/browse_thread/thread/8116b7eb264bb20a/e3299e06272ab379?hl=en&lnk=gst&q=vlookup#e3299e06272ab379 Thank you so much. This is exactly what I was looking for.