CODEWITHSUNDEEP
excelexcel-formula
R. Schreurs
R. Schreurs

Reputation: 9065

Excel LOOKUP matches the wrong cell

I am trying to achieve a fairly simple task in Excel, but I do not get the results that I want. I have a simple schedule in which I assign one of a pool of coaches to a series of matches, by filling out a simple table. Here is scaled-down version:

  Match | John | Pete | Chris |       
 -------|------|------|-------|------- 
      1 | X    |      |       | John  
      2 |      | X    |       | Pete  
      3 | A    |      | X     | Chris 
      4 | X    |      | A     | Chris (!)
      5 |      | X    | A     | Pete

Legend: X: will coach; A: is available.

I used the table to register availability and then changed one A to an X in each row, to select the person that will actually coach the match.

For an overview, I decided to add a column in which the selected coach would appear. I used the following formula: =LOOKUP("X"; B2:D2; B$1:D$1) for row 2 and copied it to the other rows so that the row numbers of each row corresponded with the row in which the formula was placed.

To my surprise, match 4 became assigned to Chris, whereas John has an X and Chris only an A.

When I read Microsoft's documentation on LOOKUP, I noticed a few things:

  1. LOOKUP has a vector form and an array form. Microsoft recommends using HLOOKUP for the array form, but I use the vector form. I do not think that HLOOKUP is useful for me, as it only looks for values in the first row of the specified array, whereas my first row contains the values to be returned.
  2. It reads: "A range that contains only one row or one column.", but also "Important: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent."

I think that 2. is what causes the issue. I am not sure how to sort a range parameter like A2:A4. Microsoft documents a SORT function, but it's beta. Also, I think that sorting the search row will mess up the match, anyway.

The workaround I found is changing my codes to A: assigned and B: backup, in which A and B are chosen to be alphabetically ascending. If I change the formulas to use lookup value "A", this gives me:

  Match | John | Pete | Chris |       
 -------|------|------|-------|------- 
      1 | A    |      |       | John  
      2 |      | A    |       | Pete  
      3 | B    |      | A     | Chris 
      4 | A    |      | B     | John  
      5 |      | A    | B     | Pete

which is the result I want.

Can anyone shed some light on this completely counter-intuitive behavior and/or describe alternative ways to achieve this?

Notes:

  1. I take care of only putting 1 X in each row.
  2. I have seen this in Microsoft Excel for Office 365 MSO (16.0.11328.20362) 64-bit and in Microsoft Excel 2016 MSO (16.0.12130.20232) 32-bits.

Upvotes: 1

Views: 1479

Answers (2)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

LOOKUP does a binary search and therefore it returns A. We have had a long discussion on Chandoo.org forum which you can read here: https://chandoo.org/forum/threads/how-vlookup-works.18378/

And here's another discussion here: http://www.ashishmathur.com/return-an-exact-value-via-the-lookup-function/

Basically, it keeps looking for an equal or lower value and then keeps slicing through data and therefore it needs data to be sorted.

You can still use LOOKUP by tweaking like below.

=LOOKUP(2;1/(B2:D2="X");B$1:D$1)

Upvotes: 4

Rory
Rory

Reputation: 34055

LOOKUP is doing exactly what it was designed to, per the documentation. You should use INDEX and MATCH:

=INDEX($B$1:$D$1;MATCH("X";B2:D2;0))

The final 0 argument to MATCH means that you are looking for an exact match so the data doesn't need to be sorted.

Upvotes: 5

Related Questions