MathGroup Archive 2012

[Date Index] [Thread Index] [Author Index]

Search the Archive

vlookup like function sugestion


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



  • Prev by Date: Re: Dynamic list creation
  • Next by Date: Re: Integers that are the sum of 2 nonzero squares in exactly 2 ways
  • Previous by thread: Re: How do I stop Mathematica changing the format of fractions in Input cells?
  • Next by thread: Re: Integers that are the sum of 2 nonzero squares in exactly 2 ways