Index and Match Formula or any other option

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
434
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi All,

We have the following sheet

Sheet1,

Test.xlsx
ABCD
1Test
215016
320479
420659
Sheet1


Sheet2, from where we want to import data

Test.xlsx
ABCDEFGHI
1Head 1Head 2Head 3Head 4Head 6Head 7Head 8Head 9
2
365489651texttext204795000.00texttext
4946598235texttext657156000.00texttext
531255448texttext326588000.00texttext
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,599
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Mar 16, 2019
Messages
434
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
pls check

Test.xlsx
ABCDEFGHIJK
1Test
215016
32047965489651texttext204795000.00texttext
420659
Sheet1
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,599
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS

ADVERTISEMENT

Try

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

Enter in D2 & drag across & down

1594633864026.png
 

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
434
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
12,599
Office Version
  1. 365
Platform
  1. Windows
Any formula will need to be dragged, Excel 2010 and 2007 do not support spill ranges.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top