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.

{=INDEX($A$1:$D$159,MATCH(1,(($A$1:$A$159=$G$2)*($B$1:$B$159=$G$3)*($C$1:$C$159=$G$4)),0),MATCH($F$5,$A$1:$D$1,0))}

 

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:

https://drive.google.com/file/d/18HUSOypv3ZPAXqpN2qyw4TPytYTB2pXp/view?usp=sharing

 

 

 

Leave a reply

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