transfer data from sheet two into sheet 1

[VBisgreat]

I have two sheets in excel workbook. Sheet2 has a list of number in col A and the corresponding data in col B C D E F. In sheet one, I want to type a number that exist in col A of sheet 2 and it automatically populate the correspond data in col B C D E F from sheet2 into sheet1.

Thanks

[JBeaucaire]

This is a standard need, I use INDEX/MATCH for it, you can also use VLOOKUP() or LOOKUP() (if your data is ordered).

Here's a sample

[VBisgreat]

Thank you so very much

-LN

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

[VBisgreat]

What happen when a number appear twice in column A and it has two corresponding row of data?

[JBeaucaire]

The whole point of an INDEX/MATCH is to find the one row of matching data to bring across needed info, like an address book or similar.

If you're needing to enter a single item and have it return a list of matching rows, this is different. INDEX/MATCH will need more instructions.

So mockup a sample workbook showing the dilemma and your desired results, point out where the results came from if not obvious in your sample data. Your example should properly demonstrate your needs in regard to multiple matches.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

[VBisgreat]

Hi,

As you can see in the attached workbook, there are two matches for number 139 in the "Codes" sheet. I want it to list all matches in "main" sheet when I type 139 there.

IndexMatch-Simple.xls

Thanks
VB

[JBeaucaire]

So, we need to add another column to your "data" sheet (CODES in this sample) that watches for your currently wanted code and indexes all the matching rows on the data sheet.

With a unique sequential index, we can pull all the matching rows over with those values.

[VBisgreat]

How come I could only enter the number once? Can you fix it so I can enter the next number on the list too?

Thanks

[JBeaucaire]

EDIT:

This isn't a macro, it's been keyed to watch the one value you enter and index the DATA sheet by that entry.

If you're going to want to enter a number anywhere in column A and get a list of any length next to it, then enter another value and get another list, then this example goes out the door. That would most likely have to be a macro.

[VBisgreat]

Thank you very much sir