It is hard to find any experienced Excel professional that does not use the INDEX()+MATCH() combination (such cases do exist though!). However, there are plenty of people sticking with the =VLOOKUP() or =HLOOKUP() and this post might be of benefit to them. The most simple use of the duo is presented — data would be obtained from a 2D table. The INDEX()+MATCH() is superior to the lookup functions in a number of ways that are going to be discussed. The combo would be my default approach as long as versions of the MS Office that support =XLOOKUP() are not widely spread.
So far in the Gas money splitting tool the workings on selecting the route have been done. Within this post the vehicle’s fuel consumption details are going to be extracted from a table by using INDEX() and MATCH().
The file attached is used going further.
On sheet Vehicles 4 different cars are listed with their technical data in various settings. Figures are well structured for reading by human beings (to be discussed at the end of the post) so the use of a table (as in the case of the Routes sheet) could have been good decision — especially if new wagons will be appearing at some time. However, for the sake of the example, it will be assumed no such event will happen. Further, 3 named ranges will be setup.
Note: In theory, the Data range could have covered the entire table (A2:E6). In such case, the other two ranges would need to include A2 as well.
Both ways can work and no advantage can be given to one over the other — in different situations either approach can get some edge but this comes down to very fine-level technical execution. Currently, non-overlapping ranges have been picked as this leaves the option for them to be separated (eg. by inserting columns between A and B). The formula will work regardless.
The inputs from the Vehicles sheet need to make their way to the Calculator sheet:
- Car used will refer to the list Vehicles_Names. It will be used to populate the drop-down from which the user will pick the car.
Note: Since it was assumed that no changes will be made, inputting the reference in its most basic form (=Vehicles!$A$3:$A$6) would have worked just as well.
- Fuel type will be taken from the Vehicles sheet with INDEX/MATCH.
- Fuel consumption > Type applied — The value in the cell must indicate what kind of road was taken (urban or not).
This rises a little structural issue — In the best-case scenario, all column headers would be eligible for the drop-down list. However, the Fuel type is not property that should be shown so the defined named range (Vehicles_Consumption_Parameters) would not be appropriate.
There are various approaches for this to be dealt with but the 3 most straightforward are:
- Defining a new named range;
- Referring to the cells as a regular range;
- Listing the options manually;
The last bullet is highly inadvisable as any change to the naming or the number of the columns would need be reflected in the hard inputs.
Once again there is no strict rule for deciding which way to go — in the example, it is assumed there will be no more columns appearing so for the sake of time, no new named range is added. However, in my opinion, using one for any sizable spreadsheet is the best course of action. When editing the drop-down, it will “talk” to the user; and if new columns are added, it can expand to list them as well.
- Fuel consumption > L/100 km — INDEX/MATCH-ed.
=INDEX() without MATCH
When searching in the vehicles table, there is two-dimensional data. Car names go vertically on the rows; while fuel consumption parameters go horizontally in the columns. Any combination between particular automobile and specification would lead to the cell that is the cross-section of the respective row and column. If the fuel type for Toyota RAV4 is needed, then relative to the entire range (named Vehicles_Fuel_Consumption_Data) the cell on the 2nd row and in the 1st column is what need be addressed.
The =INDEX(), in its most basic form, returns exactly that.
=INDEX(Range of data; Row number; Column number)
If anywhere in the file the following formula is input, it will give value Petrol:
The =MATCH() function
Finding the numbers of the row and the column is where =MATCH() comes into play. The (red) hard-inputs in the formula above must become formulas:
The function has 3 parameters and returns a numerical value as a result:
=MATCH(Value being searched; Range of data; Type of match)
- Value being searched — this is a value that presumably exists in the range set in the second function parameter. The specified car and fuel consumption will go here.
- Range of data — This is the row or column in which a value would be searched. In the problem presented — The car names will be scanned for the picked vehicle and the column headers will be matched against the consumption parameter of choice.
- Type of match — As this is not a full review of the function, it is only important for this to be set to 0. That way an exact match is what is being searched for.
Finding the relative position of the Toyota is easy:
=MATCH(“Toyota RAV4”; Vehicles_Names; 0)
Arriving at the position of the fuel type:
=MATCH(“Fuel Type”; Vehicles_Consumption_Parameters; 0)
Putting =INDEX() and =MATCH() together
Getting back to the Calculator sheet cells need to start getting populated. Extracting the fuel type combines the =INDEX() and the two =MATCH()-es from above.
Couple of small touches:
- Marked with (1) and (2) are the first arguments of each =MATCH(). In order for them to accommodate any changes (eg. another car being selected), they refer to the respective cells with data.
- As pointed by the (3) marker — the entire INDEX+MATCH is bracketed in =IFERROR(). This is to handle any error caused by missing value for the automobile or mismatch between the syntax in the Calculator and Vehicles sheets. Note on the last part — as the cars are being got from a named range, this syntax issue can only happen to the Fuel type’s row name. In case one changes its value or the one on the Vehicles sheet no =MATCH() will be possible. It is not done in the example but if there are chances of this happening the row name on sheet Calculator is best directly linked to the column header on sheet Vehicles (=Vehicles!B2).
Almost the same formula is applicable when obtaining the L/100 km consumption in a regime set by the user (eg. Extra Urban):
This way, by combining the =INDEX() and =MATCH() functions, the data needed for the calculator will be automatically populated when changes to the choices are made.
When this formula is done several times by you, it will become easily digestible and really easy to use.
INDEX+MATCH vs VLOOKUP/HLOOKUP
The cult of INDEX+MATCH goes far. Too far maybe — on RedBubble for example various master race products can be found.
Truth be told, given that VLOOKUP/HLOOKUP use single formula and much shorter syntax, the INDEX/MATCH does not always make sense — no need to complicate the work. However, the combo has several advantages.
1. It looks in any direction.
VLOOKUP/HLOOKUP need to look in the left-most/top-most part of the range to obtain a value that sits to the right/below. This is not an issue in a well-arranged table. However, often the data that is provided is not fittings our needs completely; or the same data might source different outputs that suggest matching in different columns. Cleaning the data is always a challenge and sticking with a limited amount of formulas makes it even harder.
2. Unless paired with other formula, there is hard input in the VLOOKUP/HLOOKUP.
As outlined in a previous post, hard inputs need to be evaded. However, if the lookup functions are not used with another function (either by nesting or by making reference to a cell that contains the second function), there will be hard input skewing the results any time a change is made to the data structure.
3. A more technical reason is the speed of execution — in a large data set the INDEX/MATCH will outperform the VLOOKUP. In a small one — it will have roughly the same behaviour. However, given how big spreadsheets slow down while calculating, opting for INDEX/MATCH seems reasonable.
4. The example spreadsheet is not good for showing this but if data is not sorted right, VLOOKUP might not work well with it.
5. Last but not least — INDEX/MATCH can work both vertically (as =VLOOKUP()) and horizontally (as =HLOOKUP()).
A fine understanding of the lookup functions is essential but using INDEX/MATCH can improve one’s work.
At the beginning of the post, it was pointed out that the table on the Vehicles sheet is made for consumption by humans. What is more likely is to extract/receive data that looks more like a database:
This happens to be a great advantage as this format gives more ways to extract figures (to be left for another post). Nevertheless, data can be pivoted if the representation currently available in the file is needed.