2

I've written a Google Sheets formula that looks like this:

=lookup(I6,
   {"First", "Second", "Third", "Forth", "Fifth"},
   {((D6-E6)*H6), (0.43*H6), ((D6-0.25-E6)*H6), ((D6-E6)*H6), ((D6-E6)*H6)})

And the condition for "First" works just fine but when I drag the formula down to other cells the formula fails even though "Second" exists and so on. What am I missing? The docs say the list has to be "sorted", I'm not quite sure what that means.

Here's an example.

  • Either First or Second or Third and so on. – cd-rum Jun 24 '16 at 0:59
  • My second column gets $0.00 when it should be like $2,700. H6 is 5,200.00. – cd-rum Jun 24 '16 at 1:12
  • Ha, fair enough. Link is now in the question. – cd-rum Jun 24 '16 at 1:19
2

The docs say the list has to be "sorted"

Yes, this is the problem. lookup expects the range to be searched to contain a list that is sorted alphabetically (if it contains text strings) or by value (if it contains numbers). If this expectation fails, it will not tell you anything but will likely return a wrong result.

So you have two options.

  1. (bad) Sort the search range alphabetically

Of course, you'll also need to rearrange the result range in the same way. The resulting formula is hard to read and maintain.

=lookup(I1,
       {"Fifth", "First", "Fourth", "Second", "Sixth", "Third"},
       {((D6-E6)*H6), ((D6-E6)*H6), ((D6-E6)*H6), (0.43*H6), ((D6-0.25-E6)*H6)})
  1. (good) use hlookup instead, as the documentation itself suggests

This requires a little adjustment: only one array is given, but it has two rows: first for search, the second for results. The last two arguments indicate that the result is to be taken from the second row, and that the search range is not sorted (false).

=hlookup(I1,
       {"First", "Second", "Third", "Forth", "Fifth", "Sixth"; 
       (D6-E6)*H6, 0.43*H6, (D6-0.25-E6)*H6, (D6-E6)*H6, (D6-E6)*H6, L6*H6}, 
       2, false)

(You also had a bunch of unnecessary parentheses there)

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.