hairwormman
Programmer
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...
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...