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: