3

I'm a teacher and my class have done a test. I've got some analysis of their performance in each question. I want to lookup the largest value in column B (best question) and return the question number (Column A) for that.

That works fine.

I then try the same but to find the worst question and I get the repsonse #N/A. I don't quite understand why.

The data are as follows:

    A   B
1   Q   Score
2   1   1.13
3   2   1.13
4   3   -0.94
5   4   -1.29
6   5   -1.34
7   6   -0.67
8   7   0.11
9   8   0.91
10  9a  -0.88
11  9b  -1
12  10ab    0.93
13  10c -1.21
14  11  -1
15  12  0.88
16  13  -0.83
17  14  0.94
18  15  -0.88
19  16  -0.13
20  17  0.72
21  18  -0.85
22  19  0.2
23  20  -1.32
24  21  1.19
25  22  -0.06
26  23  1.15
27  24  -0.48
28  25  0.31
29  26  1.15
30  27  0.5
31  28  1.41

The query =LOOKUP(MAX(B2:B31),B2:B31,A2:A31) returns 28, as it should, but the query =LOOKUP(MIN(B2:B31),B2:B31,A2:A31) returns #N/A.

Any advice?

3

If your data is not sorted like the example above you can use:
=OFFSET(A1,MATCH(MIN(B2:B31),B2:B31,0),0)
=OFFSET(A1,MATCH(MAX(B2:B31),B2:B31,0),0)

Even for the Max your formula will result in N/A if the maximum is not at the end of column.

Teylyn is right if the data is not sorted, correct result is pure luck, and if you use Vlookup with False the data in column A (result column) should be at the right of column B (Score first column then Q after).

With Match and Offset, there is no need for sorting just write 0 in Match to have the exact match.
enter image description here

  • I don't advise using Offset. It is volatile. Index/Match is a better approach for an unsorted range. – teylyn Jan 5 '17 at 19:53
2

Lookup requires the data to be sorted ascending. The fact that the Max variant of the formula returns the correct result is pure luck.

A lookup in a sorted range works like this:

  • Cut the data range in half and look at the last value of the first half
  • if the lookup value is smaller than that, proceed with the first half of the data, else proceed with the second half of the data
  • repeat the previous steps until only one value remains

A more comprehensive write-up of the approximate match lookup can be found here Vlookup – why do I need TRUE or FALSE?.

If the data is not sorted, then any correct result is pure luck.

Edit:

A solution with Offset, suggested in another answer, is volatile and may be the cause for slow workbooks. If the sort order of the range cannot be changed, then Index/Match is the non-volatile alternative.

=Index(A2:A31,MATCH(MIN(B2:B31),B2:B31,0))
=Index(A2:A31,MATCH(MAX(B2:B31),B2:B31,0))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.