Archive of Mr Excel Message Board

If "Custom(ised) function" does not necessarily mean some VBA code, I'd suggest using a formula that combines INDEX and MATCH functions.

Could you please explain the principles behind combining INDEX and MATCH. Many thanks.

Stephen

=============

Hi Stephen

Assume we have the following table:

{"Yaw","Pitch ","Cp13 ","Cp24";-20,30,3.42,0.03;-25,30,4.15,0.18;-30,30,5.08,0.31;-35,30,6.71,0.48}

Note that a series of values up to a semi-colon is a row of values. Enter these values into A1:D5.

The following will give you the Yaw that is associated with 6.71 which is one of the Cp13-values:

=INDEX(A2:A5,MATCH(6.71,C2:C5,1)) --> -35

6.71 is a value in column C. The assaociated value that you want to retrieve is in a column to the left of column C, in this case in A. This is a typical retrieval/lookup question where you combine INDEX and MATCH to fetch the desired value.

MATCH determines the row in which the lookup value is. The arg 1 in MATCH means TRUE (just like in VLOOKUP). Note that MATCH only applies to a vector or an array, that is, to a table consisting of a single column or a single row. INDEX is in this case give a single column range plus the row value that MATCH produces. That is, INDEX is asked to return the 4th value from the vertical array of {-20;-25;-30;-35}.

In short, VLOOKUP allows you to lookup from left to right, INDEX+MATCH from right to left wtr to multiple row and column tables.

I'd should mention that this description is not exhaustive.

Aladin