Error in lookup function?

[gzuylen]

Hi everyone,

I am doing a lookup, both the lookup vector and the result vector contain an incrementor, first rows is locked, second row not locked.
This gives the proper result until: LOOKUP(1;B$2:B8;A$2:A8) which gives 7 !

A workarroud is LOOKUP(1;1/B$2:B8;A$2:A8) this gives 3, because lookup ignores the #DIV/0! values.

Column A	Column B		=LOOKUP(1;B$2:B2;A$2:A2)
1	0		1
2	0		2
3	1		3
4	0		3
5	0		3
6	0		3
7	0		7
8	0		8
9	0		9
10	0		10

<colgroup><col span="6"></colgroup><tbody>
</tbody>

 

[barry houdini]

hello gzuylen, welcome to MrExcel

In a "regular" LOOKUP function the lookup range, in this case B2:B8, needs to be sorted ascending - as that isn't the case you may get "unexpected" results. Try INDEX/MATCH like this

=INDEX(A$2:A2;MATCH(1;B$2:B2,0))

 

[Teeroy]

I'm not sure exactly how you are required to select from column B before you find a match but you might try:

=IF(ISERROR(INDEX(A$2:A$11,MATCH(1,B$2:B2,0))),MAX(A$2:A2),INDEX(A$2:A$11,MATCH(1,B$2:B2,0)))

Note the different separator ( , versus ; ) if you are going to cut and paste.

 

[gzuylen]

Ok, thanks.

I just was wandering why Excelisfun used LOOKUP(1;1/B$2:B8;A$2:A8)
and I produced this example.

It is the sorting thing!