Matching rows against rows based on one shared cell

[steallan]

HI
If ive got two rows, which share the same info in one cell, can i match them against each other?

I.e got a big list of rows, a second big list of rows. I want to copy a row from the second list next to (horizontally) the first if the rows share the same value in one particular cell.

Is this possiblem its kind of like a vlookup function but instead of returning one cell it returns the whole row, or line of cells

thanks

 

[steallan]

help

 

[P Sitaram]

Look at this example:

Book4
	A	B	C	D	E	F	G
1	name	Value1	Value2	Value3	lvalue1	lvalue2	lvalue3
2	abc	12	56	34	100	1000	10000
3	def	45	76	54	200	2000	20000
4	ghi	23	86	65	#N/A	#N/A	#N/A
5
6
7
8
9
10	name	lvalue1	lvalue2	lvalue3
11	abc	100	1000	10000
12	def	200	2000	20000
Sheet1

The formula in E2 is:

=VLOOKUP($A2,$A$11:$D$13,COLUMN()-3,0)

which is copied down and across.

Using the COLUMN() enables the formula to be copied across to fetch successive columns. The 0 at the end ensures an exact match. The lookup data needs to be sorted ascending.

 

[steallan]

Excellent thanks a lot