Select data from one sheet to another based on index field


New Member
Dec 5, 2005
I have two spreadsheets. SHEET #1 is a table of information with approximately 400 rows and 10 columns (Lets call it THE DATABASE). The data is a listing of equipment numbers and specific associated equipment data on each row with the index being the equipment number in column A. The equipment numbers may or may not not be consecutive numbers and may not relate to the row number at all. I mean to say that equipment number 1 is not necessarily on row #1 in THE DATABASE.

My wish is to open Sheet #2 (Lets call it THE FORM) and enter an equipment number (index) in column A on any row and dynamically have the associated information for that equipment be filled into the same columns on THE FORM but be placed on the row in THE FORM where the Equipment number was entered. The data would be copied from THE DATABASE and placed into THE FORM on the row (in THE FORM) where I enter the EQUIPMENT number. I do not want to copy and paste. I want my entry of the equipment number into THE FORM bring the data from THE DATABASE and place into THE FORM on the row in the FORM where I enter the equipment number.

Your help is greatly appreciated. I hope I made this clear enough. If not please respond with further questions.

Thank You
Dave L

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying


Well-known Member
Aug 31, 2005
I think you need Vlookup.

In Sheet 2,if your Equip number is in A1, then in cell B1, enter

=Vlookup(A1,Sheet1!$A$1:$J$400,2,false), then copy formula to the right as far as needed to pull all necessary info from Sheet2. Then just change the "2", which is the column number where the info resides in Sheet1, to the next consecutive number.

Then copy all these formulas down as far as you want.

This assumes your table is in Sheet1, A1:J400. Change to suit, if necessary.

Watch MrExcel Video

Forum statistics

Latest member