LOOKUP (Array) Function (Functions in Excel)

Name	Jan	Feb	Mar
Alan	10	80	97
Bob	20	90	69
Carol	30	100	45
David	40	110	51
Eric	50	120	77
Francis	60	130	28
Gail	70	140	73

Type a Name in this cell :	Eric
The March value for this person is :	77	=LOOKUP(F12,D4:G10)

What Does It Do ?

This function looks for a piece of information in a list, and then picks an item from the

last cell in the adjacent row or column.

It always picks the data from the end of the row or column, so it is no good if you need

to pick data from part way across a list, (use VLOOKUP or HLOOKUP).

The way in which the function decides whether to pick from the row or column is based

on the size of the table.

If the table has more rows than columns :				the function will look down the left most column
				trying to find a match for the piece of information
				you asked it to look for.
				When a match is found, the function will look
				across to the right most column to pick the
				last entry on the row.
If the table has the same amount of rows and columns :
				the function will look down the left most column and
				work in just the same way as if the table had more
				rows than columns, as in the description above.
If the table has more columns than rows :				the function will look across the top row trying
				to find a match for the piece of information you
				have asked it to look for.
				When a match is found, the function will then look
				down to the bottom cell of the column to pick
				the last entry of the column.

Syntax
=LOOKUP(WhatToLookFor,RangeToLookIn)
The WhatToLookFor should be a single item.
The RangeToLook in can be either horizontal or vertical.
Be careful not to include unnecessary heading in the range as these will cause errors.
Example 1				Example 2
In this table there are more				In this table there are more columns than rows, so
rows than columns, so the				the row heading of Jan is not included in the
column heading of Jan is				lookup range.
not included in the lookup
range.

					Alan	Bob	Carol	David
	Jan			Jan	100	100	100	100
Alan	100
Bob	100
Carol	100
David	100
Eric	100
Fred	100

Formatting

No special formatting is needed.

Problems

The list of information to be looked through must be sorted in ascending order, otherwise errors

will occur, either as #N/A or incorrect results.

Table 1 shows the Name column sorted alphabetically, the results of using =LOOKUP() will

be correct.

Table 2 shows the same data, but not sorted. Sometimes the results will be correct, but other

times the result will be an #N/A error or incorrect figure.

Table 1					Table 2
Name	Jan	Feb	Mar		Name	Jan	Feb	Mar
Alan	10	80	97		David	40	110	51
Bob	20	90	69		Eric	50	120	77
Carol	30	100	45		Alan	10	80	97
David	40	110	51		Bob	20	90	69
Eric	50	120	77		Carol	30	100	45
Francis	60	130	28		Francis	60	130	28
Gail	70	140	73		Gail	70	140	73
Name :	Eric				Name :	Eric
Value :	77				Value :	45
	=LOOKUP(C88,B80:E86)					=LOOKUP(H88,G80:J86)

Related Posts by Categories