Index and Match Formula or any other option

[Vishaal]

Hi All,

We have the following sheet

Sheet1,

Test.xlsx
	A	B	C	D
1		Test
2			15016
3			20479
4			20659
Sheet1

Sheet2, from where we want to import data

Test.xlsx
	A	B	C	D	E	F	G	H	I
1	Head 1	Head 2	Head 3	Head 4	Head 6	Head 7	Head 8	Head 9
2
3	6548965	1	text	text	20479	5000.00	text	text
4	94659823	5	text	text	65715	6000.00	text	text
5	3125544	8	text	text	32658	8000.00	text	text
6
Sheet2

We want the following

if Sheet1!C2:C4 is match with Sheet2!E3:E5 then in sheet1 we will get the data of that row

help pls

 

[jasonb75]

if Sheet1!C2:C4 is match with Sheet2!E3:E5 then in sheet1 we will get the data of that row

Which parts of the data do you want where in sheet1? Please complete your example with the expected results filled in.
As you have 4 columns with text, please make them unique so that we can clearly see which is being shown where.

Which version of excel does it need to work with? 2010, 2007 or other?

 

[Vishaal]

pls check

Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		Test
2			15016
3			20479	6548965	1	text	text	20479	5000.00	text	text
4			20659
Sheet1

 

[jasonb75]

In D2, drag right and down to fill the table.

=IFERROR(INDEX(Sheet2!A$3:A$5,MATCH($C2,Sheet2!$E$3:$E$5,0)),"")

 

[gaz_chops]

Try

=IFERROR(INDEX(Sheet2!A$3:A$5,MATCH($C2,Sheet2!$E$3:$E$5,0)),"")

Enter in D2 & drag across & down

 

[Vishaal]

Thanks, its working

 

[Vishaal]

can we index sheet2!A3:K5, so that we can get the complete row without drag and drop

and can we convert it to in Array formula, we don't need to drag and drop

 

[jasonb75]

Any formula will need to be dragged, Excel 2010 and 2007 do not support spill ranges.

 

[Vishaal]

ok, thanks