BET: Microsoft Excel Functions - Lookup in Any Column

Microsoft Excel > Functions > Lookup in Any Column - OFFSET & MATCH

	This lets you lookup a value in any column and return the value in a different column (in the same row).
	The VLOOKUP (and HLOOKUP) functions can only lookup values that are in the first column (or row) of a table.
	It is possible to lookup data in other columns and rows within a table by using the OFFSET and MATCH functions.

	The OFFSET() function can be used to locate the exact cell within the table by offsetting from the column heading.
	The MATCH() function can be used to find the position of a value in a particular cell range.
	The following formula will return the corresponding First Name for a given Last Name.

=OFFSET("col_header",MATCH("match",C2:C14,0)-1,"col_offset",1,1)

	"col_header" is the cell of the column heading you are searching in (in this case the Last Name column, C2).
	"match" is the item you want to find.
	"col_offset" is the relative position of the column you want to return from the column you are using to lookup. In this case we are looking up in column 2 and returning the value in column 1, therefore the column offset is -1.
	"row_item" is the heading that you want to find in the list of row headings (B3:B12).

=OFFSET(C2,MATCH("Bell",C2:C14,0)-1,-1,1,1) = James

=OFFSET(C2,MATCH("Ellis",C2:C14,0)-1,-1,1,1) = Paul