Hey,
I have the following problem:
I have on column with various numbers of DNA fragment size (Column D) In another two columns I have a number of specific length of DNA fragments (column H) that encode specific species with their Latin name (column I).
I want to compare column D with column H. If the two values match I want the Latin species name (Column I) to be written next to the so far unidentified fragment into column E.
Thanks for your help!
Did you realize that your columns contain values just “every other row” and that the sets of rows containing values in column D and in column H respectively are disjunct?
I would take both these facts as indications of bad design, and would suggest to first clean up.
Thanks for the advice! I cleaned up “every other row” issue. I used it at fist to have a better oversight. What do you mean by columns disjunct? And how do I fix this?
Sorry. I wasn’t aware of the fact that “disjunct” is math-speak only. Sets, groups, generally containers are called disjunct if they have no common elements/members/content (“empty intersection”).
And how does this apply here? My goal is to assign the fitting species (if possible) in E by matching values in D and H?
The command “=VLOOKUP(D4;$I$3:$J$145;2;0)” so far seemed to work for me
What do you mean by columns disjunct?
You asked for the meaning of a word, and I told it.
The reason for what I originally hinted to the fact was that you had asked for the comparison of columns. An unbiased perception wouldn’t expect that this is about a match, but that a check for being equal was intended (probably in addition) such a check for two colummns needs a clear specification concerning the rows.
Over
Thanks for your reply. Yes your are correct, I was looking for matching values in order to assign the correct species. Do you have any suggestion on how to do this (the clear specification of the rows)?
So far the formula I used (=VLOOKUP(D4;$I$3:$J$145;2;0). Matched several different species in my column.
I’m afraid there is again a misunderstanding.
Since it turned out that no comparison was intended, but solely a lookup for matches, this is no longer meaningful.
Nonetheless I still would suggest do remove the (then) empty rows. No use, expectable complications.
(Of course I assume the discussed lookup isn’t the only thing you want to do with your data.)
Your formula surely matched different species, but also surely only one per row: the fiorst match.
I wouldn’t expect that you can assure every number in column H to occur only once there. Consequently I would expect numbers in some of the cells of column D with more than one match in column H. The LOOKUP() formula cannot return more than one match per application, however.
See the example I will attach to a second answer.
As already mentioned, I would expect cases of contents in column D occurring more than once in column H. In the words of the example: More than one Species may have the same ITS2 product length given under Size (bp). The LOOKUP() formula as given in the other answer will ten only find the first one of these species, and will repeat the same result if used in a different row with the same Size (bp) value in column D.
In fact you should then want (per row) a result as decribed by the pseudo-SQL statement
SELECT species from MyTable WHERE ITS2 = SizeBP
Spreadsheets aren’t made for this kind of task. Accepting some complications you can do something of the kind. If the mentioned task is assumed to only be a “first approach”, but to grow with time, you should move everything to a database.
How to do such things with limited efficiency and with expectable problems in spreadsheets is demonstrated in the attached example.
ask290231LookupWithProbablyMultipleMatches1.ods
There are altenative solutions, but none without the typical disadvantages of spreadsheets used as “fake databases”.
Thanks for the detailed and patient answers, Lupp! I appreciate it very much!
I think I solved myself:
=VLOOKUP(D4;$I$3:$J$145;2;0)
Hope this is correct!
It depends.
Lookup functions and also MATCH() return either an error or the first found item regarding the mode they are called for (third parameter in case of MATCH()).
They are doubtable if not can be asssured that a found match is the only one - or that additional matches are of no meaning.
In realistic situations it is often necessary to combine each formula of that kind with a second one checking for unambiguity or counting the alternatives.
