Mathematica Equivalent of Excel VLOOKUP function

[Charles L. Snyder]

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

[Norbert Marxer]

On 24 Okt., 08:33, "Charles L. Snyder" <clsny...@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}}]
>
> 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

Hello

One solution to your problem:

If I take your data:

Clear[mydata, lookup, f];
mydata = {{"jones", 34124324, 605, 54161, C33}, {"smith", 343214234,

550.92, 49505, V55}, {"andrews", 54554543, 550.92, 49505,

G55}, {"robinson", 565654565, 550.9, 49505, K77}, {"burt", 9898966,
752.61, 54324, U88}}; Grid[mydata]
lookup = {{54161, 3.2}, {54324, 16.3}, {49505, 12.3}}

And define the function f:

f[{a_, b_, c_, d_, e_}] := {a, b, c, d, e,
Switch[d, Evaluate[lookup /.{List -> Sequence}]]}

Then this function will give the desired output:

Grid[f /@ mydata]

This is not a general solution (where you can select the column etc),
but could be used as a starting point for the more general solution.

Best Regards
Norbert Marxer

[Albert Retey]

Charles L. Snyder schrieb:

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

If you remove the syntactically incorrect and useless Table-wrappers
from your array definitions, the following does what I think you want:

Flatten /@ Transpose[{mydata, mydata[[All, 4]] /. Rule @@@ mylookup}]

when coming form excel, you definitly should spend some time learning
the basics of mathematica, the two programs are very different. As a
starting point, you might search the documentation for ReplaceAll, Rule
and Apply, which I have used to create a list of replacement rules from
your lookup table. The rest is then only rearranging the lists to append
the result of the lookup. There are other possibilities and with very
large tables you might need to use another approach, so I expect that
you will get many other answers to that question...

hth,

albert

[Januk]

You could try something along the lines of:

mydata={{"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}};
mylookup={{54161,3.2},{54324,16.3},{49505,12.3}};

vlookup[record_, lookupTable_, recordIndex_, matchColumn_: 1,
resultColumn_: - 1] :=
Module[{nTab, key, i, tabResult},
key = record[[recordIndex]];
nTab = Length[lookupTable];

i = 1;
While[
i <= nTab && lookupTable[[i, matchColumn]] != key,
i++];

If[i <= nTab,
tabResult = lookupTable[[i, resultColumn]],
tabResult = "No Match"
];

If[MatchQ[ tabResult, _List],
Join[record, tabResult],
Append[record, tabResult]
]
]

mydata2=vlookup[#, mylookup, 4, 1, 2]&/@mydata;
mydata2//TableForm

Note that this is written so you can grab more than just a single
column from your lookup table. Also, you can compare more than just a
single column value. You can use any expression that Part will accept
for the recordIndex, matchColumn and resultColumn. Also note that you
need to be careful if your lookup table does not contain a matching
entry or if it contains more than one matching entry.

I hope that helps.
Januk

On Oct 24, 2:33 am, "Charles L. Snyder" <clsny...@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}}]
>
> 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]=

[ell...@rogers.com]

On Oct 24, 2:33 am, "Charles L. Snyder" <clsny...@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]}]

[Charles L. Snyder]

Thanks to all who replied-

...prblm solved!

clsnyder

[Charles L. Snyder]