Advertisement

Blog

Microsoft Excel: Tips & Tricks for Lookup Tables

Those of us over a certain age will remember using paper lookup tables for logarithms or trigonometry functions. Those who are younger will have been exposed to lookup tables in their programming experience. No electrical engineer can be ignorant of the approach, and most would agree that, in some cases, it is the easiest, quickest, or best way to achieve an objective.

So it should come as no surprise that Microsoft Excel includes lookup tables. What is interesting is the number of ways they can be accessed.

Tables can be structured horizontally or vertically. Like the rotation of the toilet paper on the dispenser, this is a personal preference. My tables are vertical, but whatever Excel functions I discuss here, there will be an equivalent function in the horizontal axis.

VLOOKUP (and the equivalent HLOOKUP)
This function will allow you to search a column of a table to find a matching value or — this is the elegant part — the location within an ordered table. In the search, Excel internally identifies the row, looks at a specified number of rows to the right of the initial column, and returns that value. For instance, let's assume we have a table of some of the resistor value in the E96 series, as shown below.

Figure 1

This figure also contains the associated manufacturer's part number and the engineering notation for each value. Suppose we have a calculation that returns a value of 1,213 ohms (cell A1), and we would like our function to return the nearest standard value in engineering notation (cell C1; see the formula on the fx formula bar at the top). The format of the VLOOKUP function is:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The lookup_value is the value we are trying to place in the table. As you can see, it can refer to a cell or a value. The table_array defines the whole array with the values to be searched in the first column (D3 to F27, in this case). The col_index is the number of columns to the right of the first column (which is identified as 1). The range_lookup (an optional parameter) can be true or false. When it's true, the lookup finds the nearest value. When it's false, the lookup must find the exact value. If this parameter is omitted, it defaults to true. Obviously, with the true search, the lookup values must be numerically assorted in ascending order. Notice that it returns the first value less than the tested value.

If you actually wanted to find which was the better value — the one above or the one below the calculated value — you would have to use some of the following functions to identify the cell's address, access the one below it, and evaluate the results for both.

Next Page: More Excel Tips

LOOKUP
This function is very similar to VLOOKUP, except it works only with a single input range, and it returns a value from a single output range. If we wanted to look up the manufacturer's part number, we could use a col_index_num of 3 in the VLOOKUP function on the previous page, but just to show the LOOKUP function, take a look at Figure 2.

Figure 2

The format of LOOKUP is:

LOOKUP(lookup_value, lookup_vector, [result_vector])

Its application is fairly obvious from Figure 2. These functions can look for text equalities, as well. The LOOKUP function actually doesn't have to refer to an actual table; it can include arrays of data within the statement. For instance, you could use =LOOKUP(“bump”, {“a”,1;”b”,2;”c”,3}).

I would suggest looking at the help facility if you are going to get into this. If you have worked with the if function in Excel and have gone through nested levels of the if/then/else sort of structure, you will know that it can become tedious in a hurry. As an extreme example, the if statement in this design idea is difficult enough to enter, let alone establish. The LOOKUP can provide a simpler alternative in these cases.

MATCH
The MATCH function has the same approach as LOOKUP, but it returns the position in the lookup array, rather than the actual value. Figure 3 shows its use in the formula bar, returning the value 10 in cell C5. The resistor 1.21K is indeed the 10th element in the array.

Figure 3

INDEX
INDEX will return the value at the intersection of a particular row and column in an array. Figure 4 gives an example with the formula for cell C8 in the formula bar. It is possible to have multiple tables, and then there would be an additional parameter that defines which table is being used.

Figure 4

This function returns a number that corresponds to the worksheet column (row) of a cell or range of cells — column A=1, B=2, and so forth.

TRANSPOSE
As the name implies, the function will create a horizontal range from a vertical range and vice versa. Just be aware that there are two gotchas.

First, you need to start out by blocking where you want the result. Counterintuitively, it must be the size you want; the function won't simply take the size of the array you define to copy, even though the two are equal. Second, you must treat it as an array function. In other words, you must terminate the instruction with Ctrl + Shift + Enter, instead of just Enter. The formula appears with braces around it to differentiate it from a normal formula, but you cannot add them textually. They appear with the three-fingered entry.

The nice thing is that the destination cells are related by a formula, so changing anything in the source will update in the corresponding destination. In using the transpose, you can in fact perform a function on the destination, like multiplying it by a number. Instead of entering =TRANSPOSE(C7:C12), you could enter =5*TRANSPOSE(C7:C12).

CHOOSE
The CHOOSE function is similar to LOOKUP, but it is somewhat limited. It has the format of CHOOSE(n,a,b,c…), where n is the index, and a,b,… is a list that can contain up to 254 variables (since Excel 2003). The function returns the nth item in the list, and the list cannot be a range on the worksheet.

A point I would like to make is that a lookup table is in fact nothing special in Excel. It is just more data on a worksheet. If you care to treat it as a lookup table, well good and fine. But if you want to use it in other functions, it will have no effect. There are some other functions that may prove interesting in dealing with data that is tabulated. They are ROW, COLUMN, COLUMNS, ROWS, ADDRESS, AREAS, INDIRECT, and OFFSET. However, I will leave you to investigate those on your own.

20 comments on “Microsoft Excel: Tips & Tricks for Lookup Tables

  1. RedDerek
    January 30, 2014

    Aubrey, nice post. It is this sort of thing that engineering schools do not teach. But I found very useful when I have a part and need to look up related information such as supplier and cost. I use VLOOKUP in many spreadsheet but use the term FALSE at the end instead of TRUE. The FALSE will return an exact match, else it will respond with #N/A. Which I have use the ERROR to address as well.

    The other table functions you outlined and the variant of the VLOOKUP are very well explained and I will have to now print this out as a reference. Otherwise I will have to discover when I need it. 🙂

    I look forward to your next Excel tips post.

  2. antedeluvian
    January 30, 2014

    Derek

    Thanks for pointing out the difference in the use of the TRUE/FAlse parameter. Often in Excel there are seemingly irrelevant parameters, that can make the world of difference in the outcome of the calculation.

    Your kind words are appreciated.

    I look forward to your next Excel tips post


    Typically one a month, although I am toying with the idea of inserting a different topic next month just to prove that I am not a one-trick pony.

     

  3. amrutah
    January 30, 2014

    Aubrey,

       Thanks for the tips.  I regularly make you of the INDEX and OFFSET functions for the manipulation of tables. Looking forward for next set of tips.

     

  4. SunitaT
    January 30, 2014

    @Aubrey, thanks a lot for the post. Very informative. Excel is very powerful tool and has some powerful functions and VLOOKUP is one such function. This blog is really helpful to understand VLOOKUP and other lookup functions.

  5. SunitaT
    January 30, 2014

     I use VLOOKUP in many spreadsheet but use the term FALSE at the end instead of TRUE. The FALSE will return an exact match, else it will respond with #N/A. Which I have use the ERROR to address as well.

    @RedDerek, thanks for sharing this info. Sometimes we need exact match and sometimes we need partial match and by selecting TRUE/FALSE we can easily select between partial and exact match. Is this feature available in other Match function in excel ?

  6. RedDerek
    January 30, 2014

    @SunitaT – I believe it is in the other lookup functions. I would say try and experiment. That is actually how I discovered the VLOOKUP; I needed to do some table work and discovered it. I soon figured out the TRUE/FALSE use as well.

    I always try a few variants of a function before fully relying on the use.

  7. SunitaT
    January 31, 2014

    I always try a few variants of a function before fully relying on the use.

    @RedDerek, that is the right way to learn new functions. Many times we use functions without knowing all the features of the function. I think its always better to study all the options available in that particular function before using it.

  8. SunitaT
    January 31, 2014

    Typically one a month, although I am toying with the idea of inserting a different topic next month just to prove that I am not a one-trick pony.

    @antedeluvian, I am curious to know if similar functions are present in open-source tools like open-office ? Is it possible to touch upon open-source tools like open-office which is as powerful as Excel.

  9. RedDerek
    January 31, 2014

    @SunitaT – I have used OpenOffice and it does have similar functions if I recall. The formatting of the equation is slightly different. I would say look up through the help.

  10. David Maciel Silva
    January 31, 2014

    Good job AnteDeluvian,

    We always have to have a card up his sleeve. The problem is that people think they will perform once then do not engage and schedule (dynamic tables) so that their work is less arduous.

  11. yalanand
    January 31, 2014

    @Aubrey, thanks for the post. There is slight difference between HLOOKUP and VLOOKUP. We should use HLOOKUP when comparison values are located in a row across the top of a table of data and should use VLOOKUP when our comparison values are located in a column to the left of the data we want to find.

  12. antedeluvian
    January 31, 2014

    SunitaT

    Is it possible to touch upon open-source tools like open-office which is as powerful as Excel

    I have prepared 3 more blogs on Excel. Of those one is in 3 parts and one is in two. Whether the multiparters get published in one month or spread out over several months is up to our inimitable leader, Steve. Thereafter though I am looking around for subject material and perhaps an evaluation of some open source tools may something I would look into. Of course there is always the motivation factor coupled with free time, both of which are depleted resources in my case.

  13. antedeluvian
    January 31, 2014

    yalanand

    There is slight difference between HLOOKUP and VLOOKUP. We should use HLOOKUP when comparison values are located in a row across the top of a table of data and should use VLOOKUP when our comparison values are located in a column to the left of the data we want to find.

    You are quite right, of course. I thought I had made that clear, but I looked at what I have written, and I certainly left the difference very obscure, so thanks for the clarification.

  14. antedeluvian
    January 31, 2014

    Maciel

    The problem is that people think they will perform once then do not engage and schedule (dynamic tables) so that their work is less arduous

    I know I am guilty of this. I often just use Excel for a quick calculation and don't polish it at all. Then several months later I have a similar requirment and I have to go through the same process all over again. I guess I am a slow learner. Perhaps even a bit of do what I say, not what I do.

  15. eafpres
    February 4, 2014

    Regarding the return of an error, what I now do almost 100% of the time is use a construction as follows:

    IF(NOT(ISERROR(VLOOKUP(query),VLOOKUP(query),(default)))

    What this does is avoid getting errors by testing for the error before returning a value.  (query) in the above is whatever syntax you need in the VLOOKUP for the search you are doing, and (devault) may be a value or other additional logic.

    I have used this so often that I just do it automatically now.  Also, note that once your get (query) you can copy/paste so you don't have to type it all in 2x.  This also reduces errors.

     

  16. chirshadblog
    February 5, 2014

    @Red: Open Office is good as long as its for basic functions. I have used Open Office and found that Word is very much the similar but Excel and Power Point had some major differences. Especially Power Point was so basic which I feel is not worth. Excel too lacked some advanced functions but can be managed for basic day to day operations if its being used as a 3rd party supporting tool

  17. antedeluvian
    February 6, 2014

    Blaine

    What this does is avoid getting errors by testing for the error before returning a value.  

    This is sage advice. Thanks for the tip.

  18. eafpres
    February 6, 2014

    Thanks, Aubrey.  In generalizing the form I erred on the parentheses; just to avoid confusion here is the corrected usage:

    =IF(NOT(ISERROR(VLOOKUP(query))),VLOOKUP(query),(default))

    There are unlimited cool things you can do in Excel.  The newer versions offer significant enhancements in some functions.  I'm pretty much an Excel junkie…

  19. antedeluvian
    February 6, 2014

    Blaine

     I'm pretty much an Excel junkie…

    Maybe you should be writing these blogs. I have 3 to go, then the field is open.

  20. Davidled
    February 7, 2014

    These functions make engineer job easy to organize value in the huge data of data base without any complex math. 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.