Looking up data in a table

[John Mc]

Hello Group,

I am interested in performing a lookup function using a 12 X 8 data
array. Since VLookup and HLookup will not work in this case, what
does Excel offer to lookup information in arrays? Any suggestions or
help would be greatly appreciated.

Thank you. -John

 

[Tyro]

12 x 8 array. An example of what you are looking up and what you want to be
returned would be helpful. We are not mind readers.

Tryo

 

[John Mc]

Sorry about that: Here is an example of the data we wish to query (I
wish I could upload the example!).

1A1,1A2,1A3,1A4,1A5,1A6,1A7,1A8,1A9,1A10,1A11,1A12,,0.00,0.11,0.08,0.13,0.05,0.04,0.03,0.03,0.04,0.02,0.05,0.04
1B1,1B2,1B3,1B4,1B5,1B6,1B7,1B8,1B9,1B10,1B11,1B12,,0.11,0.07,0.08,0.03,0.05,0.03,0.05,0.03,0.04,0.03,0.02,0.03
1C1,1C2,1C3,1C4,1C5,1C6,1C7,1C8,1C9,1C10,1C11,1C12,,0.07,0.01,0.05,0.03,0.03,0.02,0.03,0.02,0.02,0.01,0.04,0.03
1D1,1D2,1D3,1D4,1D5,1D6,1D7,1D8,1D9,1D10,1D11,1D12,,0.13,0.06,0.05,0.03,0.03,0.07,0.02,0.01,0.00,0.03,0.03,0.02
1E1,1E2,1E3,1E4,1E5,1E6,1E7,1E8,1E9,1E10,1E11,1E12,,0.09,0.08,0.04,0.01,0.03,0.04,0.06,0.03,0.06,0.00,0.04,0.04
1F1,1F2,1F3,1F4,1F5,1F6,1F7,1F8,1F9,1F10,1F11,1F12,,0.09,0.08,0.06,0.05,0.02,0.05,0.12,0.11,0.03,0.01,0.03,0.04
1G1,1G2,1G3,1G4,1G5,1G6,1G7,1G8,1G9,1G10,1G11,1G12,,0.06,0.06,0.05,0.05,0.02,0.02,0.02,0.03,0.03,0.03,0.04,0.20
1H1,1H2,1H3,1H4,1H5,1H6,1H7,1H8,1H9,1H10,1H11,1H12,,0.05,0.06,0.06,0.05,0.04,0.02,0.03,0.02,0.01,0.02,0.03,0.03


I wish to query 1A1 and for Excel to return 0.00, 1A2 and return 0.11,
etc. (see top dataline).
The 0.00 datapoint is 13 cells to the right of the 1A1 cell.
I guess my question is to find out if Excel has a VLookup-type
function that can search an entire multicolumn array and return values
X cells to the right. My problem is that the normal VLookup function
can only search down a single column and the entire 12 X 8 array of
data. I hope this is more clear.

Thanks again.

-John

 

[Tyro]

You still have not showed us what you are looking up and what you want to be
returned.

Tyro

 

[John Mc]

Ok, here is my best shot:

I have a column of unique gene names (96 in total). I also have a
matrix of data (12 X 8) containing information about each gene (ex.
person's height). The matrix is organized such that the top row
contains 12 columns of unique data corresponding to the gene names 1
through 12. The second row contains another 12 columns of unique data
corresponding to gene names 13 to 24. I wish to transform that matrix
of data into a single column of information next to the unique gene
name column. Now, I am able to make a matrix (12 X 8) of the gene
names such that if the two matrices were super imposed the individual
gene name would match the corresponding data point. Now, I undestand
that I could do this by copying and pasting. However, I will need to
do this for hundreds of matrices so anything to automate the process
is essential (for my sanity!).

I can't use either VLOOKUP or HLOOKUP in this situation (the 12 X 8
matrix), because each of these functions can search only in one
direction. Does Excel have the capability to search within the matrix
(ie. the value present in the column of unqiue names) relative to the
matrix of gene names that sits adjacent to the matrix of gene
information data (ex. height) and return a value X spaces away.

Thanks again. I hope this was more clear. I appreciate your time
in reading this issue!

Have a great weekend.

-John

 

[John Mc]

Hi Group,

I found a solution to linearize a matrix of information in Excel.
See the following website:
http://www.cpearson.com/excel/MatrixToVector.aspx

The solution is to use the OFFSET function. Sweet solution.

-John