Transposing matching lists onto 1 horizontal line of data

**mparker123** · 11-05-2014, 12:23 PM

Hello,

I have two columns of data, names and numbers:

Tom 642
Ram 484
Matt 526

The numbers are linked with the names. I want to be able to have a cell, where I can put in the name and it will provide me their number.

I am currently using this for the two columns: =INDEX(O$#:O$#,MATCH(D#,P$#:P$#,0)) however I would like a code to allow me to do this if all of the data is on one line like this:

Tom, Ram, Matt, 642, 484, 526

So I still want excel to recognise the cell A1 (tom) is matched with the number in A4 (642).

Thanks,
Matt

**FDibbins** · 11-05-2014, 12:40 PM

See is this example will give you what you want...

	A	B	C	D	E	F
1	aa	bb	cc	11	22	33
2	cc
3	33

A2 is your search criteria
A3=OFFSET(A1,0,MATCH(A2,1:1,0)+COUNT(1:1)-1)

**ChemistB** · 11-05-2014, 12:52 PM

Another option, in my example, data is in A1:B?

In D1, copied to the right

=IFERROR(INDEX($A$1:$B$500,MOD(COLUMNS($A$1:A$1)-1,COUNTA($A:$A))+1, INT((COLUMNS($A$1:A$1)-1)/COUNTA($A:$A))+1),"")
Does that work for you?