Quote of the Day
Men acquire a particular quality by constantly acting a particular way … you become just by performing just actions, temperate by performing temperate actions, brave by performing brave actions.
— Aristotle
Figure 1: Picture of 38999 25/37 Connector with Pins Labeled.
I almost sent out a spreadsheet today that contained a VLOOKUP error – I forgot that VLOOKUP is not case sensitive. Fortunately, I caught my error seconds before I hit send. Today's post is about how I chose to perform a case-sensitive lookup in an ancient version of Excel.
My application is simple. I am working with a round connector that assigns lower and upper case letters to the pin positions (Figure 1). I have a list of coordinate positions for each pin label. I also have a separate list with pin labels that are assigned to lasers. I want a list of the (x, y) coordinate of every laser. Normally, I would use Power Query and a join to perform the lookup, however, my customer is forcing me to use an ancient version of Excel that does not support Power Query.
There are many ways to solve this problem. A quick Google search led me to this web page that gave a solution based on an array function that contains the Index/Match/Exact functions to solve my problem. I liked this solution because it is generally applicable. Figure 2 shows how I this approach to lookup pin X-values using the transmit pin name and a column of all the pin names. The Y-value lookup is similar. My example uses tables and structured references.
Figure 2: My Lookup Function for X-Values Using Transmits Names.
I include a greatly simplified version of my original Excel work here.