Lookup tables and the Wildcard Character

[pmacgovern]

Introduction

Lookup tables are used very frequently in both the Compensation and Variable Pay modules to pull non-employee data into worksheets - budget percentages is the most common use, but certainly not the only one. Often times, the Wildcard Character '*' is used in an input coluimn. However, it might not work quite how you think.

System Behaviour

The system documentation indicates the way lookup tables find results:

While this is technically true, it is important that the Wildcard Character doesn't override exact matches - so a wildcard in the first row might be overridden by an exact match in a lower row.

Consider the following lookup table:

if there is a column with the following formula:

lookup("table",job,grade,user,1)

The system will search from top to bottom in column A then when it finds a match for a value in column A it will continue to see if can find an exact match before moving to column B. If it cannot find an exact match, then it uses the first Wildcard row.  Here is the result from using this lookup table:

I would imagine that most consultants, given the data in Sandy Ago's record, would assume that the lookup statement would return "User", as the first row of the lookup table ( *, *, ago1) would appear to match. However, since there is an exact match of Job (MGR-IT) in row 5 (and the rest of the row matches with its wildcards), then that row is pulled from the table. Basically, "MGR-IT, *, *" trumps "*, *, ago1", even though they both technically match.

If you change the table to this:

then you get the following results:

Conclusion

When using wildcards, the order of columns matters. If the first column is to contain wildcards, be careful to put the most "exact" matches at the top of the lookup table.

Credit

Many thanks to Skip Jones for doing the research upon which this blog is based.