Tools: functions VLOOKUP, HLOOKUP
Level: intermediate
Problem
You downloaded data sets for two stock market indices, DJIA and NASDAQ, and now want to put both indices on a same chart. However, DJIA data does not have gaps for weekends and holidays, while NASDAQ is provided for days when stock trading actually took place. This means you cannot copy and paste two ranges, as they are of different size. Dates will not match. Is there a way to put this data together without manually matching every day between both data sets?
| A | B | C | D | |
| DJIA | NASDAQ | |||
| 1-Jan | 3-Jan | |||
| 2-Jan | 4-Jan | |||
| 3-Jan | 5-Jan | |||
| 4-Jan | 8-Jan | |||
| 5-Jan | 9-Jan |
Solution
- (OPTIONAL) Active cell A1.Type "Date".
- (OPTIONAL) Active cell B1. Type "DJIA".
- (OPTIONAL) Active cell C1. Type "NASDAQ".
- Starting down column A from cell A2, make a sequential date series that will be the first column in the table where you will consolidate data. Date series do not need to match precisely the date series in the two data sets you have, put in whatever dates you want to see in the final data set and chart.
- Sort both data sets by date in ascending order.
- (OPTIONAL) Name DJIA data set "djia"
- (OPTIONAL) Name NASDAQ data set "nasdaq"
- Active cell B2. Type =VLOOKUP($A2,djia,2).
- Copy cell B2 to cell C2.
- Edit range name in cell C2 to =VLOOKUP($A2,nasdaq,2).
- Copy cells B2 and C2 all the way down your date range.
You will get the following results:
| A | B | C | |
| 1-Jan | #N/A | ||
| 2-Jan | #N/A | ||
| 3-Jan | |||
| 4-Jan | |||
| 5-Jan | |||
| 6-Jan | |||
| 7-Jan | |||
| 8-Jan | |||
| 9-Jan | |||
| 10-Jan | |||
| 11-Jan |
We now have a table where data from both data sets are matched to its date series. January 3 to January 5 results are the same for DJIA and NASDAQ as in their original data sets.
When VLOOKUP does not find in the data set the date it is searching for, it finds instead the closest match – that is, the previous date, since we sorted both data sets by date. So if your data set ends before the date series ends, VLOOKUP will fill the remainder with the last value in the data set. This is what happened to DJIA set that ends on January 5, when we searched for January 6 and beyond. For the same reason, NASDAQ filled Saturdays and Sundays with Friday data – which, strictly speaking, makes sense.
If you are searching for dates that are earlier than the earliest date you are searching for, you are getting an N/A error.
Formula syntax
| Code | Variable | Comment |
| =VLOOKUP( | ||
| $A2 | Lookup_value | Value that you are looking for in the table_array |
| djia | Table_array | Table where the Lookup_value and the values that correspond to it are located |
| Col_index_num | Number of the column in the Table_array where the value that you want the formula to return is located | |
| Range_lookup | Optional: 1 (default) is an approximate match, 0 is an exact match | |
| ) | Formula complete |
Make 1 look as "Q1"
Tools: Number Format Codes
Level: intermediate
Problem
At times you want to display numbers with text strings before or after. However, after you add a string, you realize you can no longer sum or multiply these numbers, or use them for lookup, and the formula bar no longer shows a numeric value inside the cell. Excel converted numbers and labels into text strings. After you delete the text and spaces, the number is back a number – but you still need these labels.
There is a way to get into a cell with the number anything you want, while it will still remain a number. For this and several subsequent recipes, we will use built-in number format codes.
Solution
- Use Format-Cells-Number menu or Number-Category ribbon command.
- On Number tab in Category list, find the last item – it will be Custom. Click it.
- To the right, there will be a text field over a list of lines of symbols. Delete whatever is in this field.
- In an empty field type "Q"#. Whatever is inside double quote marks will be treated by Excel as a label that will be added to whatever is in the cell.
- Press Enter or click OK.
You just created a custom number format. Whatever you type in this cell, and any other cell to which you will apply this format, will be displayed as "Q12345" instead of "12345". However, only the displayed vide will change. Inside the cell, the raw number will stay what it is – a number.
Similar tasks
Task 1. Make "1000" looks as "1000 US Dollars".
Solution:
#" US Dollars"
Note that the first character inside is a space. If you omit it, you will get instead (predictably) "1000US Dollars".
Make 12125551212 look as "+1 (212) 555 - 1212"
Tools: Number Format Codes
Level: intermediate
Problem
Phone numbers, social security numbers, credit card numbers and many other numbers have dashes, spaces, brackets and other non-digit symbols to make them more legible. However, if you type the symbols in an Excel cell, they will wreak havoc on the results – some will be interpreted as math operators, some will force conversion to text, some will just cause a #NAME error.
To display a phone number as a phone number a special number format may be used.
Solution
- Enter in active cell value "12125551212". This will be the phone number we are formatting.
- Use Format-Cells-Number menu or Number-Category ribbon command.
- On Number tab in Category list, find the last item – it will be Custom. Click it.
- To the right, there will be a text field over a list of lines of symbols. Delete whatever is in this field.
- In an empty field type +0 (000) - 000 - 0000.
- Press Enter or click OK.
Your phone number should now look as "+1 (212) 555 – 1212".
Similar tasks
Task 1. Format 77775555551111 as 7777 555555 1111
Solution: use number format code
0000 000000 0000
Make 1000 look as "1M"
Tools: Number Format Codes
Level: intermediate
Problem
Large numbers are frequently represented at a smaller order of magnitude – e.g. 1 000 000 = "1 million".
You are preparing company financials and do all calculation with a 1 cent precision. However, in a final report the numbers are represented "in millions of dollars".
Can you shed off these three significant zeroes without dividing every result by 1 000 000?
Solution
- Enter in active cell value "123456789".
- Use Format-Cells-Number menu or Number-Category ribbon command.
- On Number tab in Category list, find the last item – it will be Custom. Click it.
- To the right, there will be a text field over a list of lines of symbols. Delete whatever is in this field.
- In an empty field type #, (# is pronounced "hash"). Observe that the number preview instantly changed from 123456789 to 123457. Excel rounded it to the next thousand.
- Type another comma, so that format code now looks as #,,. Observe that the preview number is now 123.
- Type.# so that the format code becomes #,,.#. The preview will change to 123.5 – now Excel rounds the number to the next hundred thousand.
- Type another #, format code should be #,,.##. Preview will display 123.46, rounded to the next ten thousand.
- Type another #, for a format code #,,.###. Preview should change to 123.457, rounded to next whole thousand.
- I believe you understood how to change the look of the number by now. Delete or add commas and hashes until the preview number will look the way you want.
- If you are undecided, restore format code to #,, and then type " M". Your final number format will look as #,," M".
- Press Enter or click OK.
Your number will now display as 123 MM.
Note: the correct symbol order in code will be decimals first, thousands next, so 123.457 should actually be #.###,,. However, Excel will convert the syntax for you even if you do not do it right.
Similar tasks
Task 1.
Make 1000 look as "1 thousand".
Solution:
#," thousand"
Task 2.
Make 123 456 789 look as "123.5"
Solution
#.#,,
Make 123 look as "000000123"
Tools: Number Format Codes
Level: intermediate
Problem
You need to assign each item in your list a unique 9-digit ID. Excel, however, truncates trailing zeroes, and after you type 000000123, all what is left in cell is 123. Is it possible to get around this problem?
Solution
In addition to "soft" digits, coded as # (hash), number format code also includes a hard digit symbol, coded as 0. If a zero is included in the format, then the formatted number will always have the same number of digits in this place. If a number has insignificant zeroes either before or after the significant digits, they will be displayed. This is what we need.
- Enter in active cell value "123".
- Use Format-Cells-Number menu or Number-Category ribbon command.
- On Number tab in Category list, find the last item – it will be Custom. Click it.
- To the right, there will be a text field over a list of lines of symbols. Delete whatever is in this field.
- In an empty field type 000000000.
- Press Enter or click OK.
123 should now display as 000000123.
Similar tasks
Task 1.
Display 0.1 as 0.100
Solution
0.000
Task 2.
Display 0.1 as.1
Solution
To suppress insignificant zeroes, use hash (#) instead of zero.
#.#
