1

Here is the screenshot of my excel workbook

enter image description here

I do not understand why the value in cell j7 is 44 ?

j7 formula is =LOOKUP(1,(TRIM($D$2:$D$9)=TRIM(H7))/(TRIM($E$2:$E$9)=TRIM(I7)),$F$2:$F$9)

The result of the two arrays division is the following

{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}/ {TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE} = {1;#DIV/0!;0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!} Right ?

So I am looking for 1, basically the formula becomes

LOOKUP(1,{1;#DIV/0!;0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!},$F$2:$F$9)

Hence the result should be 10 but not 44 . . . . . ?

EDIT

When i correct my formula to =LOOKUP(1,1/(TRIM($D$2:$D$9)=TRIM(H7))/(TRIM($E$2:$E$9)=TRIM(I7)),$F$2:$F$9)

it works fine . Why ? Thank you everybody for giving the alternative solutions with match and index. I just can't understand why my first formula did not work. Any why when i add 1/ it MAGICALLY works ? ? ?

Buras
  • 3,069
  • 28
  • 79
  • 126
  • Can you explain your business need? It looks like you can acheive the same result with a simple sort. – Mr. Mascaro Oct 06 '14 at 20:19
  • My vector is not in ascending order, which means that the `lookup` should stop right after it encounters `#DIV/0!;` . . hence stop at the first value – Buras Oct 06 '14 at 20:27
  • But when the _lookup_value_ is NOT found within the _lookup_vector_, the LAST value in the _lookup_vector_ less than the _lookup_value_ is returned. Where did you hear otherwise? Where on earth did you get this idea that LOOKUP "stops at the first value" in such circumstances? Can you post the link to this site? – XOR LX Oct 06 '14 at 22:44

3 Answers3

3

If the values are not in ascending order, and you are looking for a value within the range of values (as opposed to a value larger than anything in the range), LOOKUP may give unexpected results.

A different way to return the desired result is with a combination of INDEX and MATCH:

=INDEX($F$2:$F$9,MATCH(1,(TRIM($D$2:$D$9)=TRIM(H7))/(TRIM($E$2:$E$9)=TRIM(I7)),0))

entered as an array formula with ctrl-shift-enter

Note that with MATCH, looking for an exact match, the range does not need to be sorted.

Another formula that will return the correct results, and can be normally entered (assuming no duplicate entries in the first table):

=SUMPRODUCT((TRIM(H7)=TRIM($D$2:$D$9))*(TRIM(I7)=TRIM($E$2:$E$9))*$F$2:$F$9)
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
2

The explanation is:

with the first formula, the first array in the Lookup function contains zeros after the 1 value, in the third and sixth value of the array:

enter image description here

Lookup expects data to be sorted ascending and will return the first item less than or equal to the search value, starting from the last value in the array. In this case that is the zero value in the sixth position of the array.

The edited formula results in an array that contains only one number "1". All other values are Div errors. So the position of that "1" value is what Lookup will use.

enter image description here

Further explanation:

In your first formula you divide two arrays that contain TRUE or FALSE and the result contains 1, 0 and Div error values.

{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}/
{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE} =
{1;#DIV/0!;0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!}

Including the 1/ in the formula will divide the first array of TRUE and FALSE values by 1, which returns an array that consists of either 1 or Div errors. Further dividing that array by the second array will only return 1 or Div errors, no zeros. The steps are

1/{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}/{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE} results in

{1;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}/{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE} results in

{1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

No zeros!

teylyn
  • 34,374
  • 4
  • 53
  • 73
1

As mentioned, LOOKUP expects the values in the lookup_vector to be in ascending order. To gain the first match of columns H & I to columns D & E, I would suggest mathematically excluding the non-matching rows. What is left would be the matching rows. The following example supplies the first double match.

For J2

=INDEX($F$2:$F$9,MIN(INDEX(ROW($1:$8)+(($D$2:$D$9<>H2)+($E$2:$E$9<>I2))*1E+99,,)))

Fill down as necessary. This is a standard formula and can be easily modified to supply the 2nd, 3rd, etc matching values by swapping SMALL() in place of MIN(). Your results should be close to the following.

       enter image description here