7/27/2019 Lookup.xlsx

1/2

Excel Function Dictionary 1998 - 2000 Peter Noneley

LOOKUP (Array)Page 1 of 2

1234

56789

1011121314151617181920212223242526272829

303132333435363738394041

424344454647484950

A B C D E F G H I J

LOOKUP (Array)

Name Jan Feb Mar Alan 10 80 97

Bob 20 90 69Carol 30 100 45David 40 110 51Eric 50 120 77

Francis 60 130 28Gail 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 thelast 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 needto 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 basedon the size of the table.

If the table has more rows than columns : the function will look down the left most columntrying to find a match for the piece of informationyou asked it to look for.When a match is found, the function will look

across to the right most column to pick thelast entry on the row.

If the table has the same amount of rows and columns :the function will look down the left most column andwork in just the same way as if the table had morerows than columns, as in the description above.

If the table has more columns than rows : the function will look across the top row tryingto find a match for the piece of information youhave asked it to look for.When a match is found, the function will then look

down to the bottom cell of the column to pickthe 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.

7/27/2019 Lookup.xlsx

2/2

Excel Function Dictionary 1998 - 2000 Peter Noneley

LOOKUP (Array)Page 2 of 2

5152535455

565758596061626364656667

686970717273747576777879

808182838485868788899091

A B C D E F G H I JExample 1 Example 2In this table there are more In this table there are more columns than rows, sorows than columns, so the the row heading of Jan is not included in thecolumn heading of Jan is lookup range.not included in the lookup

range. Alan Bob Carol DavidJan Jan 100 100 100 100

Alan 100Bob 100

Carol 100David 100Eric 100Fred 100

FormattingNo special formatting is needed.

ProblemsThe list of information to be looked through must be sorted in ascending order, otherwise errorswill occur, either as #N/A or incorrect results.

Table 1 shows the Name column sorted alphabetically, the results of using =LOOKUP() willbe 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 2Name Jan Feb Mar Name Jan Feb Mar

Alan 10 80 97 David 40 110 51Bob 20 90 69 Eric 50 120 77

Carol 30 100 45 Alan 10 80 97David 40 110 51 Bob 20 90 69Eric 50 120 77 Carol 30 100 45

Francis 60 130 28 Francis 60 130 28Gail 70 140 73 Gail 70 140 73

Name : Eric Name : Eric

Value : 77 Value : 45=LOOKUP(C88,B80:E86) =LOOKUP(H88,G80:J86)