"Queries" in Excel
In my one of my last articles, I discussed how and why you might want to use named ranges. In that post, however, I did not discuss one of the main reasons I use named ranges: lookup tables. The heading of this post does not relate to the "Data | Import External Data | New ? Query...", but rather to the possibilities relating to quickly looking up a data item in a table. For example, perhaps you have a listing of states such as:
PA, Pennsylvania
CA, California
FL, Florida
NY, New York
...
If you (for whatever reason) wanted to type PA somewhere in your spreadsheet and have another cell determine what the state name is, you could use a VLOOKUP formula to do just that. If your data were horizontal, such as:
PA, CA, FL, NY...
Pennsylvania, California, Florida, New York...
You could instead use HLOOKUP (H for horizontal, V for vertical). We'll discuss VLOOKUP, but HLOOKUP is exactly the same except for the transposition of the table. To use VLOOKUP, three arguments are required and one argument is optional:
1. The value you want to look up (PA in our example).
2. The table where Excel should look for the value.
3. The OFFSET from the beginning of the table where the value is located*.
4. Does the value to lookup need to be exact, or can it be as close as possible?
* This requires a little more explaining. Essentially, you are looking up a value in a given table. When you highlight the table, you will have a certain number of rows (in our example, 4 rows) and you will have a certain number of columns (in our example, 2 columns). This argument is asking for the column in which it should return the value if a match is found (in our example, we wanted the 2nd column so that the lookup would return the name of the state).
Therefore, to execute the formula we would write "=VLOOKUP(A1, States!$A$1:$B$4, 2 FALSE)". This assumes that we want to look up the name of the state for the abbreviation located in cell A1 from the table on the States sheet, cells A1 - B4, we want it to return the 2nd column (the state names) and the match as to be EXACT. A close enough match would not be appropriate; for example, NY might be closest to NJ (if NY were missing in the table) and that would return New Jersey for NY, where an error would be more appropriate.
The reason I mention named ranges is because this formula would be much more readable if you changed the "States!$A$1:$B$4" part of the formula to (instead): "State_Table". To do this, you could name the range on the states tab by highlighting cells A1 to B4, hitting Ctrl-F3, and then naming the range "State_Table". Again, the 2 main reasons to use named ranges are: 1) Clarity and 2) Ease of verification. The formula would now read "=VLOOKUP(A1, State_Table, 2, FALSE)".
This example is fairly simplistic, but a more realistic example might include a listing of stock tickers along with the company name as well as the exchange that the stock is traded on, etc. You could type the ticker (or perhaps the company name is easier to remember), and then have a formula look everything else up for you.
The few main things to remember are:
1. The value you want to look up MUST be in the first column of the table.
2. You can only look up values in columns that are included in the table. For example, you could not look up a third column in our first example as we only included two columns in the formula (recall: "States!A1:B4" references 2 columns - A & B).
3. The last argument of the formula is very important if you want exact values. You might not want exact values, perhaps, if you were looking up bond ratings and ratios between x and y were in this particular rating, and those between a and b are in another rating. Exact lookups, on the other hand, would be required if you were looking up ticker symbols; a "close-enough" match wouldn't really make sense in that case.
4. If you're using an HLOOKUP formula, the 3rd argument refers to which row to return (not which column as in a VLOOKUP).
Anyway, hope this gives you a little insight into how you can get "mini-queries" in Excel!
PA, Pennsylvania
CA, California
FL, Florida
NY, New York
...
If you (for whatever reason) wanted to type PA somewhere in your spreadsheet and have another cell determine what the state name is, you could use a VLOOKUP formula to do just that. If your data were horizontal, such as:
PA, CA, FL, NY...
Pennsylvania, California, Florida, New York...
You could instead use HLOOKUP (H for horizontal, V for vertical). We'll discuss VLOOKUP, but HLOOKUP is exactly the same except for the transposition of the table. To use VLOOKUP, three arguments are required and one argument is optional:
1. The value you want to look up (PA in our example).
2. The table where Excel should look for the value.
3. The OFFSET from the beginning of the table where the value is located*.
4. Does the value to lookup need to be exact, or can it be as close as possible?
* This requires a little more explaining. Essentially, you are looking up a value in a given table. When you highlight the table, you will have a certain number of rows (in our example, 4 rows) and you will have a certain number of columns (in our example, 2 columns). This argument is asking for the column in which it should return the value if a match is found (in our example, we wanted the 2nd column so that the lookup would return the name of the state).
Therefore, to execute the formula we would write "=VLOOKUP(A1, States!$A$1:$B$4, 2 FALSE)". This assumes that we want to look up the name of the state for the abbreviation located in cell A1 from the table on the States sheet, cells A1 - B4, we want it to return the 2nd column (the state names) and the match as to be EXACT. A close enough match would not be appropriate; for example, NY might be closest to NJ (if NY were missing in the table) and that would return New Jersey for NY, where an error would be more appropriate.
The reason I mention named ranges is because this formula would be much more readable if you changed the "States!$A$1:$B$4" part of the formula to (instead): "State_Table". To do this, you could name the range on the states tab by highlighting cells A1 to B4, hitting Ctrl-F3, and then naming the range "State_Table". Again, the 2 main reasons to use named ranges are: 1) Clarity and 2) Ease of verification. The formula would now read "=VLOOKUP(A1, State_Table, 2, FALSE)".
This example is fairly simplistic, but a more realistic example might include a listing of stock tickers along with the company name as well as the exchange that the stock is traded on, etc. You could type the ticker (or perhaps the company name is easier to remember), and then have a formula look everything else up for you.
The few main things to remember are:
1. The value you want to look up MUST be in the first column of the table.
2. You can only look up values in columns that are included in the table. For example, you could not look up a third column in our first example as we only included two columns in the formula (recall: "States!A1:B4" references 2 columns - A & B).
3. The last argument of the formula is very important if you want exact values. You might not want exact values, perhaps, if you were looking up bond ratings and ratios between x and y were in this particular rating, and those between a and b are in another rating. Exact lookups, on the other hand, would be required if you were looking up ticker symbols; a "close-enough" match wouldn't really make sense in that case.
4. If you're using an HLOOKUP formula, the 3rd argument refers to which row to return (not which column as in a VLOOKUP).
Anyway, hope this gives you a little insight into how you can get "mini-queries" in Excel!
posted by ktr at
10:49 AM
0 Comments:
Post a Comment
<< Home