No more VLookup? (TGPGet)

VLookup is often a pain point for many Excel users, due to its complicated use and unintuitive setup. It is a powerful function that allows a user to find specified information in a lookup table. This is \

TGPGet is a much simpler solution to the same problem. It has 4 fields to be populated:

• SearchValue (Required): The Value or String that you are looking for.
• WhichGPTable (Optional): This field is to select the table to search for the value, you can enter any table in the Company Database
  • There are some aliases that you can utilize for this field:
    • CUS = RM00101 
    • ITM = IV00101 
    • VEN = PM00200 
    • ACC = GL00100 JOIN GL00105 
    • FA   = FA00100 
  • If this field is empty, then the function will search in the above 5 fields using the parameters below.
• FieldToRetrieve (Optional): This field is situationally optional. If the WhichGPTable is one of the aliases shown then this field may be left blank.
  • Under these conditions the following will apply:
    • CUS → CUSTNAME
    • ITM → ITEMDESC
    • VEN → VENDNAME
    • ACC → ACTDESCR
    • FA   → ASSETDESC
  • If the WhichGPTable uses a custom company table then this field must be populated.

• KeyFieldToSearch (Optional): Any field in the data table can be used to search
  • If any alias is used then the following apply:
    • CUS → CUSTNMBR
    • ITM → ITEMNMBR
    • VEN → VENDORID
    • ACC → ACTINDX or ACTNUMST
    • FA   → ASSETID 

Some examples of the syntax and results are as follows:

• =TGPGet(“100XLG”)
  • Green Phone
• =TGPGet(123) 
  • 000-4131-00 
• =TGPGet(“000-4131-00”) 
  • Canadian Sales – Installation Charges 
• =TGPGet(“CHAIR”, ”IV00101”, ”STNDCOST”) 
  • 81.39