You are here:

Excel/lookup a single criteria and return multiple rows

Advertisement


Question
Hi, let' say I have the following data in my excel in Sheet 1:

Code     ID     Name     Age     Profession
12345    246    Adam     24      Engineer
34567    357    Tom      37      Doctor
12345    135   Bryan    30    Engineer
23456    468   Gina    21    Teacher
34567    579   Sylvia    26    Nurse
12345    680   Jarod    29    IT Specialist
23456    791   Stacy    32    Counselor
23456    802   Robert    42    Lawyer
45678    913   Tricia    37    Manager

In Sheet 2 on the other hand, I have the following:
Code   ID   Name   Age   Profession
12345
23456
34567
45678

Is there a way to look up the values(criteria) in the A column which is the Code in Sheet 2 and get the data in Sheet 1 which meets the criteria and at the same time automatically insert in new rows since there are more than 1 items to be returned for each criteria? I greatly appreciate if you could help or advice the formulas that I could use for this!

Answer
There are no formula that automatically change your pre-existing entries on sheet2.   To do this with formulas only I would think you could do 1 code at a time only.  You would type a code in A2 and all the rows would appear for that code.  To see another code you would change the code in A2 and the new data would appear.  You could copy that data off elsewhere if you wish.

Truth be told, I wouldn't even do that much work.  I would use Sheet1 still for all this.  I  would turn on the DATA > FILTER and just filter the CODE column for any code(s) at one time.

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.