Mathematica 9 is now available
Services & Resources / Wolfram Forums / MathGroup Archive
-----

MathGroup Archive 2008

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

Search the Archive

Mathematica Equivalent of Excel VLOOKUP function

  • To: mathgroup at smc.vnet.net
  • Subject: [mg93020] Mathematica Equivalent of Excel VLOOKUP function
  • From: "Charles L. Snyder" <clsnyder at gmail.com>
  • Date: Fri, 24 Oct 2008 02:28:21 -0400 (EDT)

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}}]

I have found some old posts that come close to a solution, but aren't
quite right:

makeLookupTable[symbol_, list_,
   column_] := (symbol[___] = Table[0., {Length[list[[1]] - 1]}];
   Scan[With[{key = #[[column]]}, symbol[key] = Delete[#, column]] &,
    list]);
m1 = Table[{i, Random[], Random[]}, {i, 10}];
m2 = Table[{Random[], Random[], Random[], i}, {i, 2, 12}];
allkeys = Union[m1[[All, 1]], m2[[All, 4]]]
Join[{#}, lt1[#], lt2[#]] & /@ allkeys

Thanks in advance

clsnyder


  • Prev by Date: Re: Expressions with ellipsis (...)
  • Next by Date: Re: How to reduce memory usage of the MathKernel.exe?
  • Previous by thread: Re: Contourplot3d of a list of expressions
  • Next by thread: Re: Mathematica Equivalent of Excel VLOOKUP function