Pulling data from multiple cells from sheet2 to sheet1 using INDEX function?

Hello All,

I am using the formula.. =INDEX(Sheet2!B2:Y2,MATCH(Sheet1!A1,Sheet2!B2:C2))

I would like for it to act as a database function, or try to. On Sheet1 Cell A1 I would like to type in a name, it would look up the name on Sheet2 and pull all information on this persons name into Sheet1 B1 thru y2. At the moment it only pulls in one cell of information and not the entire row needed.

I have tried every option I can think of to adjust the formula to make it work. Does anyone have a solution or fix for this formula. Maybe I'm making this harder than it is.

Is there is a different function or VB code that could be used i place of what I am using.

Thanks

Henry

so in the new sheet1 cell B1
=INDEX(Sheet2!B$1:B$500,MATCH(Sheet1!$A1,Sheet2!$A$1:$A$500))
so in the new sheet cell C1
=INDEX(Sheet2!C$1:C$500,MATCH(Sheet1!$A1,Sheet2!$A$1:$A$500))
so in the new sheet cell D1
=INDEX(Sheet2!D$1:D$500,MATCH(Sheet1!$A1,Sheet2!$A$1:$A$500))

you need to enter the formula as array using control+shift+enter and you will get {} around the formula

then you can actually just enter the formula in cell A1 and copy down
then copy all those cells across to column Y

see attached example

Great, thanks etaf. Will try this out and let you know how it works.