Finding data in Excel with INDEX()+MATCH()

It is hard to find any expe­ri­enced Excel pro­fes­sion­al that does not use the INDEX()+MATCH() com­bi­na­tion (such cas­es do exist though!). How­ev­er, there are plen­ty of peo­ple stick­ing with the =VLOOKUP() or =HLOOKUP() and this post might be of ben­e­fit to them. The most sim­ple use of the duo is pre­sent­ed — data would be obtained from a 2D table. The INDEX()+MATCH() is supe­ri­or to the lookup func­tions in a num­ber of ways that are going to be dis­cussed. The com­bo would be my default approach as long as ver­sions of the MS Office that sup­port =XLOOKUP() are not wide­ly spread.

So far in the Gas mon­ey split­ting tool the work­ings on select­ing the route have been done. With­in this post the vehi­cle’s fuel con­sump­tion details are going to be extract­ed from a table by using INDEX() and MATCH().

The file attached is used going further.

On sheet Vehi­cles 4 dif­fer­ent cars are list­ed with their tech­ni­cal data in var­i­ous set­tings. Fig­ures are well struc­tured for read­ing by human beings (to be dis­cussed at the end of the post) so the use of a table (as in the case of the Routes sheet) could have been good deci­sion — espe­cial­ly if new wag­ons will be appear­ing at some time. How­ev­er, for the sake of the exam­ple, it will be assumed no such event will hap­pen. Fur­ther, 3 named ranges will be setup.

Note: In the­o­ry, the Data range could have cov­ered the entire table (A2:E6). In such case, the oth­er two ranges would need to include A2 as well.

Both ways can work and no advan­tage can be giv­en to one over the oth­er — in dif­fer­ent sit­u­a­tions either approach can get some edge but this comes down to very fine-lev­el tech­ni­cal exe­cu­tion. Cur­rent­ly, non-over­lap­ping ranges have been picked as this leaves the option for them to be sep­a­rat­ed (eg. by insert­ing columns between A and B). The for­mu­la will work regardless.

The inputs from the Vehi­cles sheet need to make their way to the Cal­cu­la­tor sheet:

• Car used will refer to the list Vehicles_Names. It will be used to pop­u­late the drop-down from which the user will pick the car. 

Note: Since it was assumed that no changes will be made, inputting the ref­er­ence in its most basic form (=Vehicles!$A$3:$A$6) would have worked just as well.

• Fuel type will be tak­en from the Vehi­cles sheet with INDEX/MATCH.
• Fuel con­sump­tion > Type applied — The val­ue in the cell must indi­cate what kind of road was tak­en (urban or not).

This ris­es a lit­tle struc­tur­al issue — In the best-case sce­nario, all col­umn head­ers would be eli­gi­ble for the drop-down list. How­ev­er, the Fuel type is not prop­er­ty that should be shown so the defined named range (Vehicles_Consumption_Parameters) would not be appropriate. 

There are var­i­ous approach­es for this to be dealt with but the 3 most straight­for­ward are:

1. Defin­ing a new named range;
2. Refer­ring to the cells as a reg­u­lar range;
3. List­ing the options manually; 

The last bul­let is high­ly inad­vis­able as any change to the nam­ing or the num­ber of the columns would need be reflect­ed in the hard inputs.

Once again there is no strict rule for decid­ing which way to go — in the exam­ple, it is assumed there will be no more columns appear­ing so for the sake of time, no new named range is added. How­ev­er, in my opin­ion, using one for any siz­able spread­sheet is the best course of action. When edit­ing the drop-down, it will “talk” to the user; and if new columns are added, it can expand to list them as well.

• Fuel con­sump­tion > L/100 km — INDEX/­MATCH-ed.

=INDEX() without MATCH

When search­ing in the vehi­cles table, there is two-dimen­sion­al data. Car names go ver­ti­cal­ly on the rows; while fuel con­sump­tion para­me­ters go hor­i­zon­tal­ly in the columns. Any com­bi­na­tion between par­tic­u­lar auto­mo­bile and spec­i­fi­ca­tion would lead to the cell that is the cross-sec­tion of the respec­tive row and col­umn. If the fuel type for Toy­ota RAV4 is need­ed, then rel­a­tive to the entire range (named Vehicles_Fuel_Consumption_Data) the cell on the 2nd row and in the 1st col­umn is what need be addressed.

The =INDEX(), in its most basic form, returns exact­ly that.

=INDEX(Range of data; Row num­ber; Col­umn num­ber)

If any­where in the file the fol­low­ing for­mu­la is input, it will give val­ue Petrol:

=INDEX(Vehicles_Fuel_Consumption_Data;2;1)

The =MATCH() function

Find­ing the num­bers of the row and the col­umn is where =MATCH() comes into play. The (red) hard-inputs in the for­mu­la above must become formulas:

=INDEX(Vehicles_Fuel_Consumption_Data;MATCH();MATCH())

The func­tion has 3 para­me­ters and returns a numer­i­cal val­ue as a result:

=MATCH(Val­ue being searched; Range of data; Type of match)

• Val­ue being searched — this is a val­ue that pre­sum­ably exists in the range set in the sec­ond func­tion para­me­ter. The spec­i­fied car and fuel con­sump­tion will go here.
• Range of data — This is the row or col­umn in which a val­ue would be searched. In the prob­lem pre­sent­ed — The car names will be scanned for the picked vehi­cle and the col­umn head­ers will be matched against the con­sump­tion para­me­ter of choice. 
• Type of match — As this is not a full review of the func­tion, it is only impor­tant for this to be set to 0. That way an exact match is what is being searched for.

Find­ing the rel­a­tive posi­tion of the Toy­ota is easy:

=MATCH(“Toy­ota RAV4”; Vehicles_Names; 0)

Arriv­ing at the posi­tion of the fuel type:

=MATCH(“Fuel Type”; Vehicles_Consumption_Parameters; 0)

Putting =INDEX() and =MATCH() together 

Get­ting back to the Cal­cu­la­tor sheet cells need to start get­ting pop­u­lat­ed. Extract­ing the fuel type com­bines the =INDEX() and the two =MATCH()-es from above.

Cou­ple of small touches:

• Marked with (1) and (2) are the first argu­ments of each =MATCH(). In order for them to accom­mo­date any changes (eg. anoth­er car being select­ed), they refer to the respec­tive cells with data.
• As point­ed by the (3) mark­er — the entire INDEX+MATCH is brack­et­ed in =IFERROR(). This is to han­dle any error caused by miss­ing val­ue for the auto­mo­bile or mis­match between the syn­tax in the Cal­cu­la­tor and Vehi­cles sheets. Note on the last part — as the cars are being got from a named range, this syn­tax issue can only hap­pen to the Fuel type’s row name. In case one changes its val­ue or the one on the Vehi­cles sheet no =MATCH() will be pos­si­ble. It is not done in the exam­ple but if there are chances of this hap­pen­ing the row name on sheet Cal­cu­la­tor is best direct­ly linked to the col­umn head­er on sheet Vehi­cles (=Vehicles!B2).

Almost the same for­mu­la is applic­a­ble when obtain­ing the L/100 km con­sump­tion in a regime set by the user (eg. Extra Urban):

This way, by com­bin­ing the =INDEX() and =MATCH() func­tions, the data need­ed for the cal­cu­la­tor will be auto­mat­i­cal­ly pop­u­lat­ed when changes to the choic­es are made.

When this for­mu­la is done sev­er­al times by you, it will become eas­i­ly digestible and real­ly easy to use.

INDEX+MATCH vs VLOOKUP/HLOOKUP

The cult of INDEX+MATCH goes far. Too far maybe — on Red­Bub­ble for exam­ple var­i­ous mas­ter race prod­ucts can be found.

Truth be told, giv­en that VLOOKUP/HLOOKUP use sin­gle for­mu­la and much short­er syn­tax, the INDEX/MATCH does not always make sense — no need to com­pli­cate the work. How­ev­er, the com­bo has sev­er­al 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 val­ue that sits to the right/below. This is not an issue in a well-arranged table. How­ev­er, often the data that is pro­vid­ed is not fit­tings our needs com­plete­ly; or the same data might source dif­fer­ent out­puts that sug­gest match­ing in dif­fer­ent columns. Clean­ing the data is always a chal­lenge and stick­ing with a lim­it­ed amount of for­mu­las makes it even harder.

2. Unless paired with oth­er for­mu­la, there is hard input in the VLOOKUP/HLOOKUP.

As out­lined in a pre­vi­ous post, hard inputs need to be evad­ed. How­ev­er, if the lookup func­tions are not used with anoth­er func­tion (either by nest­ing or by mak­ing ref­er­ence to a cell that con­tains the sec­ond func­tion), there will be hard input skew­ing the results any time a change is made to the data structure.

3. A more tech­ni­cal rea­son is the speed of exe­cu­tion — in a large data set the INDEX/MATCH will out­per­form the VLOOKUP. In a small one — it will have rough­ly the same behav­iour. How­ev­er, giv­en how big spread­sheets slow down while cal­cu­lat­ing, opt­ing for INDEX/MATCH seems reasonable.

4. The exam­ple spread­sheet is not good for show­ing this but if data is not sort­ed right, VLOOKUP might not work well with it.

5. Last but not least — INDEX/MATCH can work both ver­ti­cal­ly (as =VLOOKUP()) and hor­i­zon­tal­ly (as =HLOOKUP()).

A fine under­stand­ing of the lookup func­tions is essen­tial but using INDEX/MATCH can improve one’s work.

At the begin­ning of the post, it was point­ed out that the table on the Vehi­cles sheet is made for con­sump­tion by humans. What is more like­ly is to extract/receive data that looks more like a database:

This hap­pens to be a great advan­tage as this for­mat gives more ways to extract fig­ures (to be left for anoth­er post). Nev­er­the­less, data can be piv­ot­ed if the rep­re­sen­ta­tion cur­rent­ly avail­able in the file is needed.