Which table function can replace VLOOKUP in excel?

Table-valued functions in finaquant .net libraries

Which table function can replace VLOOKUP in excel?

Postby ariston » 15 Dec 2014, 17:33

Hi

I am now testing finaquant's excel add-in for table valued functions.

Which table function can be used for joining tables like VLOOKUP in excel? For example, assume I have a table with product ID and other product attributes like vendor, brand etc. I want to add another product attribute to this table like "color" which is stored in another table with fields ID and color.

Arin
ariston
 
Posts: 6
Joined: 20 Oct 2013, 13:51

Table Function "Combine Tables" for VLOOKUP in excel

Postby tunc » 26 Dec 2014, 15:05

Hi Arin

Combine Tables is the table function in Excel you are looking for (after installing related add-in for Excel).

See the example below where you append an additional attribute named color to your product table:

Image

Important: The number format of the id columns of the tables above must be set to Text so that the add-in can understand that id is an integer attribute (i.e. not a key figure). See section Creating Tables Manually in Excel in the User Guide for table-valued functions in Excel.

You can combine two excel tables (ListObject) named ProductTbl (including fields id and product) and ProductColorTbl (including fields id and color) with the table function Combine Tables as shown below:

Image

The resultant combined table named ProductWColor (right-most table starting from column I):

Image

Note that 0 as an id value in the table ProductColorTbl plays the role of a match-all value; the color green (with id = 0) is matched to all products whose id's (and colors) are not explicitly stated in the color table.

Tunc
tunc
 
Posts: 25
Joined: 22 Jul 2014, 19:29
Location: Switzerland


Return to Table Functions

Who is online

Users browsing this forum: No registered users and 2 guests

cron