@@@URGENT@@@ Custom Function - Vlookup
Posted by Chris Allison on March 29, 2001 8:39 AM
I am creating a customised function to look up a value.It will be done in a similar way as Vlookup.WIthin the function that im going to create Im am want to have a vlookup to search for data. I want it to find data thats 3 columns to the left of the name that is to be looked up. I want to then use this data in other calculations where i will be dividing and multiplying that value
Posted by Aladin Akyurek on March 29, 2001 9:29 AM
If "Custom(ised) function" does not necessarily mean some VBA code, I'd suggest using a formula that combines INDEX and MATCH functions.
Posted by Stephen Giles on March 30, 2001 4:42 AM
Could you please explain the principles behind combining INDEX and MATCH. Many thanks.
Stephen
Posted by Aladin Akyurek on March 30, 2001 6:57 AM
=============
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
