Miscellaneous Excel

Back to Miscellaneous questions.	Back to Lookup functions.

Here is the data table. The person inquiring wanted to input two variables: Category (in the top row) and a number that appears under that category. Once these two variables were input, he wanted to return the value in column B.

In the example below, Category = BB and Number = 25. With those two variables, he wanted to return the value "ALPHA".

This formula will return the required value:
=INDIRECT(ADDRESS(MATCH(D11,OFFSET($B$2:$B$6,0,MATCH(F11,$C$2:$G$2,0)),0)+1,2))

"Barrie", you say, "that's great. Can you translate this formula in to English for me?".

"OK", says I, "this will be long-winded, but let's break it down in to its parts".

MATCH(F11,$C$2:$G$2,0) - finds the position of the value in F11 relative the range C2:G2.
The syntax is MATCH(lookup_value,lookup_array,match_type) where
• Lookup_value is the value you use to find the value you want in a table (F11 in this example).
• Lookup_array is a contiguous range of cells containing possible lookup values (C2:G2 in this example).
• Match_type is the number -1, 0, or 1. (-1) finds the smallest value that is greater than or equal to lookup_value (Lookup_array must be placed in descending order). (0) finds the first value that is exactly equal to lookup_value. (1) finds the largest value that is less than or equal to lookup_value (Lookup_array must be placed in ascending order).
In this example, it returns the value of 2.
OFFSET($B$2:$B$6,0,MATCH(F11,$C$2:$G$2,0)) - which equates to OFFSET($B$2:$B$6,0,2)
OFFSET returns a reference to a range. OFFSET doesn't actually move any cells or change the selection; it just returns a reference.
The syntax is OFFSET(reference,rows, cols,height,width) where
• Reference is the reference from which you want to base the offset. In this case, the reference range is B2:B6.
• Rows is the number of rows, up or down, that you want the upper-left cell to refer to. In this case, I don't want to adjust the row of my range.
• Cols is the number of columns, to the left or right, that you want the upper-left cell of the result to refer to. In this case, I want to adjust my range 2 columns to the right. The number 2 was derived from my MATCH function.
• Height is the height, in number of rows, that you want the returned reference to be. Height must be a positive number.
Width is the width, in number of columns, that you want the returned reference to be. Width must be a positive number.
These options allow you to change the size of your referenced range.
If height or width is omitted, it is assumed to be the same height or width as reference.
In this example, it returns the range of D2:D6.
MATCH(D11,OFFSET($B$2:$B$6,0,MATCH(F11,$C$2:$G$2,0)),0) - which equates to MATCH(D11,D2:D6,0)
See my explanation above on the MATCH function.
In this example, it returns the value of 2.
ADDRESS(MATCH(D11,OFFSET($B$2:$B$6,0,MATCH(F11,$C$2:$G$2,0)),0)+1,2) - which equates to ADDRESS(2+1,2) - which equates to ADDRESS(3,2).
ADDRESS creates a cell address as text, given specified row and column numbers. The syntax is ADDRESS(row_num,column_num,abs_num,a1,sheet_text) where
• Row_num is the row number to use in the cell reference.
• Column_num is the column number to use in the cell reference.
Row and column number are required arguments. The following are not:
• Abs_num specifies the type of reference to return. 1 = Absolute, 2 = Absolute row; relative column, 3 = Relative row; absolute column, 4 = Relative. If this is omitted, it is assumed to be 1.
• a1 is a logical value that specifies the A1 or R1C1 reference style. If a1 = 1 (or omitted), ADDRESS returns an A1-style reference; if a1 = 0, ADDRESS returns an R1C1-style reference.
• Sheet_text is text specifying the name of the worksheet to be used as the external reference. If sheet_text is omitted, no sheet name is used.
In this example, it returns the address B3.
FINALLY, the last part. INDIRECT(ADDRESS(MATCH(D11,OFFSET($B$2:$B$6,0,MATCH(F11,$C$2:$G$2,0)),0)+1,2)) - which equates to INDIRECT(B3).
INDIRECT returns the reference specified by a text string. The syntax is INDIRECT(ref_text,a1) where
• Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. In this case, it is a reference to a cell as a text string.
• A1 is a logical value that specifies what type of reference is contained in the cell ref_text. If a1 = 1 (or omitted), ref_text is interpreted as an A1-style reference. If a1 = 0, ref_text is interpreted as an R1C1-style reference.