LOOKUP remembers the wrong thing...

[hairwormman]

Hey Y'all:
This is best explained by an example

Data sheet 1:

Sample Result
100 atta
101 cccc
103 tcga
106 tttt
(Note data is ordered by sample, and NOT all samples have a result(note that samples 102, 104 and 105 do not have entries).
Now, I want to populate data sheet 2, which contains ONLY the sample data (sample is still in order but contains ALL samples).

Data sheet 2:

Sample Result
100
101
102
103
104
105
106

I put the following formula into column B:
LOOKUP(A2, 'sheet 1'!A:A,'sheet 1'!B:B)
This is what I would expect:

Data sheet 2:

Sample Result
100 atta
101 cccc
102
103 tcga
104
105
106 tttt

This is what I get:

Data sheet 2:

Sample Result
100 atta
101 cccc
102 cccc
103 tcga
104 tcga
105 tcga
106 tttt

Excel fills in the fields which are to be empty with the value is used for the previous entry so 102 is to be blank (or N/A or error) but is filled in with the same entry it gave to 101; the same is true for sampels 104 and 105- it fills in the entry for sample 103). I have tried ISNA or ISNULL but since EXCEL is convinced that the "empty" samples have a result, it promply ignores those statements. Help, and thanks.

Ben the worm...

 

[dneufarth]

from help

If LOOKUP can't find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.


Dave

 

[dneufarth]

try here for a solution

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=446

Dave

 

[xlbo]

use VLOOKUP instead with the 4th argument set to FALSE. This will look for an exact match - if it cannot find one you will get a #N/A error. This can then be masked by using an IF statement:

=IF(ISNA(Vlookup_Formula),"",Vlookup_Formula)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[hairwormman]

Thanks for replying. I have made it work by using the VLOOKUP command. If anyone is interested, the formula I ended up using is below.

Cheers...Ben the worm

=IF(ISNA(VLOOKUP(C2,'Seq data'!$A$2:$H$961,2,FALSE)),"", VLOOKUP(C2,'Seq data'!$A$2:$H$961,2,FALSE))