Index and Match For Complex Search in Excel

For a complicated database search in excel, use of index and match is superior to any of other lookup technique.

Following is the general search layout with this method.

= index ( table_array, match(1,((condition_row1=columnsearch1)*(condition_row2=columnsearch2)…),0),match(conditionsearch,columnhearder_array,0)

In the following example, table of pipe type, pipe class, outer diameter and bore diameter is given:

To find the bore diameter with the help of pipe type, pipe class, and outer diameter following should executed.



Once the above formula is entered, hit Ctrl+Shift+Enter to execute this function.

In the above case, we want to know the bore diameter of pipetype = “HDPE, PE 100”, Pipe Class = “10′ and Outer Diameter = ’32’. From table orange color highlights the required diameter of 24.1, and this function shows the value of 24.1 in cell G5.

This method is useful in Civil Engineering. For example : to get the required value which is appended with multiple information, like to determine loading (P, M3) generated from a SAP2000 output of frame, station, load case, load type, load condition etc. or find out any parameter from a steel table corresponding to given section name, weight per linear distance, etc.


View the above sample file:




Leave a reply

Your email address will not be published. Required fields are marked *