write the name of the child

[mgm90210M]

this table is contain the data of every child
(with no formula inside that cell) :

cell a1	high	age	iq	grate in history	grate in biology	grate in geography
tomes	6	8	77	77	76	98
stave	7	4	123	65	45	54
brus	3	38	145	98	87	32
dan	7	7	66	65	65	65

<tbody>
</tbody>

i need that when i write the name of the child in second table (in column a )all that data from the first table about that child will write in the second table , like this :

cell a7
tomer	6	8	77	77	76	98
dan	7	7	66	65	65	65

<tbody>
</tbody>

thanks

 

[konew1]

In B7 and copy down and across
=INDEX($A$1:$G$5,MATCH($A15,$A$1:$A$5,0),COLUMN())

 

[L. Howard]

Another way.

Select cell B8 to G8 and while still selected type in this formula--DO NOT HIT ENTER.

While still selected hold down ctrl and shift keys and then hit enter. This is array enter. You will get {....} around the formula.

Pull down as far as needed.

If you want to make corrections or adjustments to the formula, re-select B8 to G8, make your changes and array enter again.

=IF(ISERROR(VLOOKUP(C20,$C$13:$I$16,{2,3,4,5,6,7},0)),"",(VLOOKUP(C20,$C$13:$I$16,{2,3,4,5,6,7},0)))

Regards,
Howard

 

[mgm90210M]

i sorry it is not work

 

[oldbrewer]

name the range A2 to G5 "results", then use vlookup - but you would have to put names in alphabetical order first. If you can't do that, use an offset match equation. Something like =offset($A$1, match(1,a6,a2:a5,0) - this would be placed to the right of the name you select......

this starts at A1, looks for the name that you put into cell A6 in the block of cells A2 to A5 and returns the value in column B opposite the name you are searching for - copy it across changing 1 to 2,3,4,5 as appropriate

 

[oldbrewer]

cell a1	high	age	iq	grate in history	grate in biology	grate in geography
tomes	6	8	78	77	76	98
stave	7	4	123	65	45	54
brus	3	38	145	98	87	32
dan	7	7	66	65	65	65
	high	age	iq	grate in history	grate in biology	grate in geography
brus	3	38	145	98	87	32
	OFFSET($A$1,MATCH($A11,$A$2:$A$5,0),1)
	OFFSET($A$1,MATCH($A11,$A$2:$A$5,0),2)
	OFFSET($A$1,MATCH($A11,$A$2:$A$5,0),3)
	OFFSET($A$1,MATCH($A11,$A$2:$A$5,0),4)
	OFFSET($A$1,MATCH($A11,$A$2:$A$5,0),5)
	OFFSET($A$1,MATCH($A11,$A$2:$A$5,0),6)	these are the formulas that give you each score for the selected name

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[konew1]

i sorry it is not work

If you want additional help you should at least say what you expected and what result you got. You had two suggestions, one from me and I know it works, and one from L Howard that needs some minor correction to the cell addresses but it works as a formula. Make sure the cell addresses in the formula are pointing to your data. Use the excel help function to understand the formula if it is new to you so you can check it, and learn for the future

 

[konew1]

this starts at A1, looks for the name that you put into cell A6 in the block of cells A2 to A5 and returns the value in column B opposite the name you are searching for - copy it across changing 1 to 2,3,4,5 as appropriate

I haven't tried this formula, but can suggest a shortcut for when entering it. Instead of changing 1,2,3 etc as you copy across use COLUMN() and then as you copy across the number returned by xl will increase for each column. Sometimes you need to start with COLUMN()-COLUMN($xx)+1 if you are not starting in A:A. Change the xx to the first column of your data. It is a big help if you have a lot of columns, and it can future-proof your spread sheet if you ever need to add additional columns

 

[mgm90210M]

i dont understand where to write the formula

 

[konew1]

Excel Workbook
	A	B	C	D	E	F	G
1	cell a1	high	age	iq	grate in history	grate in biology	grate in geography
2	tomes	6	8	77	77	76	98
3	stave	7	4	123	65	45	54
4	brus	3	38	145	98	87	32
5	dan	7	7	66	65	65	65
6
7	Put formula in B8 and copy to B8:G9
8	tomes	6	8	77	77	76	98
9	dan	7	7	66	65	65	65
10
11
12	This table copied from posting #1
13	cell a7
14	tomer	6	8	77	77	76	98
15	dan	7	7	66	65	65	65
Sheet1