Beyond Excel's (BXL's) Function:

Lookup

Description:

This acts very much like VLOOKUP(sFind, sTable, 2, FALSE) where the table's first column contains an ID, Name, Code, or some other unique identifier and the second column contains a value we can use as a parameter for our queries. I call tables with this structure Property Tables. Property Tables can have more than two columns but for the purposes of this function, we only look at the first two. This function is crafted so it does matter what the column headings are.

Parameters

This function has two parameters: Table and Find. Table is our property table's name. Find is the unique identifier that fncLookup searches for. in the first column that we will search for.

Example:

let

RowCount = fncLookup("tblMP", "Periods"),

DynTbl = fncCrtTblRows(RowCount, "Prd")

in

DynTbl

Code:

/* Description:This is the equivalent of =VLOOKUP(sFind, sTable, 2, FALSE)

Requisites: Excel Table's first column contains sFind and second column contains

value to return. Columns can be named whatever we wish.

Inputs: sTable: Name of Excel table to search

sFind: Value to look for in first column

Outputs: Value: "Value" column's contents

Date Ini Description

04/04/18 CWH Original Development

*/

(sTable as text, sFind as text) =>

let

WorkBook = Excel.CurrentWorkbook(), /* Get current workbook's objects */

XLTable = WorkBook{[Name=sTable]}[Content], /* Get XL Table from workbook */

NewHeaders= Table.DemoteHeaders(XLTable), /* Change column names to Column1...n */

RmvOldHdrs= Table.RemoveFirstN(NewHeaders), /* Remove old column names */

TblRecord = RmvOldHdrs{[Column1=sFind]}, /* Find record where 1st column equals sFind */

Value = TblRecord[Column2] /* Return second column's value */

in

Value