Expanding a list with VLOOKUP

This is a simple solution to a silly problem. There may be a better way to solve it, but it works and requires miminal skill.

Problem: Someone gives you a file containing a partial description of what's in a 384-well plate. Yet you need to place that within the context of a full 384-well plate description. (the more general problem is how to graft small lists onto larger lists when the lists have a common matching element)

For example, the list below is missing even-numbered wells, and some rows (plate row B). You might have to convert the list below such that the second column has all 384-well plate positions: A01, A02, A03, etc through P24.

Plate	Well	Name	Gene	Len	Tm	Chr
18	A01	YAL034C-B		70	72.4	I
18	A03	YAL042C-A	KRE23	70	72.4	I
18	A05	YAL044W-A		70	76.5	I
18	A07	YAL056C-A	KRE20	70	76.5	I
18	A09	YBL029C-A		70	75.9	II
18	A11	YBL071W-A	KTI11	70	74.2	II
18	A13	YBL108C-A		70	77.7	II
18	A15	YBR058C-A	TSC3	70	74.7	II
18	A17	YBR085C-A		70	74.2	II
18	A19	YBR103C-A		70	73.6	II
18	A21	YBR233W-A	DAD3	70	74.2	II
18	A23	YBR255C-A		70	77.7	II
18	C01	YCL001W-B		70	71.8	III
18	C03	YCL021W-A		70	76.5	III
18	C05	YCL027C-A		70	74.2	III
18	C07	YCL057C-A		70	76.5	III
18	C09	YCR038W-A		70	75.3	III
18	C11	YCR097W-A		70	69.5	III
18	C13	YDL085C-A		70	74.7	IV
18	C15	YDL114W-A		70	70.6	IV
18	C17	YDL159W-A		70	71.8	IV
18	C19	YDL185C-A		70	71.8	IV
18	C21	YDL240C-A		70	73	IV
18	C23	YDL247W-A		70	70.1	IV

Rather than go through manually and insert all the required rows, you can use excel and the VLOOKUP function to graft your list onto a full 384-well plate list. The idea is create an index for the incomplete list, and then use vlookup to compare the two 384 well lists and copy the index to the complete list when a match is found. Once the index is in place across the full 384 well list, the file can be sorted by index, which puts the matching part of the full 384 well list in the same order as the partial 384 well list. The two lists can then be combined to the same document, then sorted by the full 384 well list column. This action has the side effect of decompressing the partial list across the full list, thus providing blank lines in all the appropriate places. This can be accomplished as follows:

open the file with excel (file 1), sort by Well.
insert two blank columns on left edge.
copy the well locations to the first column to use as an index for vlookup
in the second column make an ascending integer list
open a new worksheet (file 2) and create a full 384 well plate list (A01-P24, 384 entries), sort ascending.
in the second column insert the VLOOKUP function with the following criteria: =VLOOKUP(A1, 1st two columns of file 1, 2, FALSE)
- The first argument to vlookup is the value to look up in a table array specified by the second argument.
- The second argument is a table of consisting the first two columns of file 1, also referred to as a table array. The first column of this table will be searched for the specified value.
- The third argument to vlookup is the column of the table array from which a value should be returned, if a match is found by the lookup). In this case you want to return the index value you made - which should be in the second column.
- The fourth argument (optional) specifies whether an approximate match should be returned incase an exact match can't be found.
use fill down to copy the formula to each cell of the second column.
When this is done, some of the cells in column 2 will have a match and others will not.
sort the sheet by the index. This sheet with the full 384 well plate list, now has a portion which matches the order of the partial 384 well list.
Go back to the partial list (file 1) and copy everything but the first two columns
paste it along the matching edge of the full list.
Now you can get rid of any extraneous columns, and when you sort the list by the column containing the full 384 well plate designation, the "partial list" portion will be expanded/sorted into a full 384 well format.

Chris Seidel