Need help with =if formula

[sindel]

Good afternoon,

I'm a total newbie and am struggling with the correct formula to use, so please forgive me.

I have two sheets in a workbook. The first sheet is titled Responses and contains 3 columns: timestamp, Student ID number, and pass type. The second sheet is titled Names and also contains three columns: StudentID, Firstname, Lastname.

What I want to do:
*Enter unique student ID number value in the Student ID number cell on sheet one.
*Excel finds the exact number value on sheet two.
*Excel looks to the left of the matched Student ID, copies the First Name, and Last Name from those columns.
*Displays those retrieved first name and last name values on sheet one.

Basically, I'm trying to create a student pass system where the kids check in by number, and Excel populates their name for them to save time.

I clicked in the Responses sheet, cell D1 and tried the formula =if(Responses!B:B,Names!A:A), but I don't understand how to finish the formula to display the kids' first and last names instead of the text StudentID.

Many thanks!

 

[GerryZ]

hello can yuo post a small sample with the expected outcome!

 

[sindel]

I've already figured out the timestamp and school portion of sheet one, so please don't focus on those.

Outcome:
Timestamp / ID# / School / First Name / Last Name
09:52am / 20089800 / Washington / Jan / Smith
10:12am / 20074123 / Lewis / Brian /Miller


My second sheet currently contains about 6000 student names arranged by row and is functioning as a quasi-database:
20089800 Jan Smith
20074123 Brian Miller

My problem boils down to... when I type 20089800 (or other unique number) into sheet one, I want the corresponding student's first name and last name to appear in columns D and E of sheet one, respectively.

 

[GerryZ]

Copy across!

Book1
	A	B	C	D	E
1	Timestamp	ID#	School	First Name	Last Name
2	09:52am	20089800	Washington	Jan	Smith
3	09:52am	20089801	asdf	paolo	asf
4	09:52am	20089802	afdsa	frank	asfd
5	09:52am	20089803	asdf	joe	zucca
6	09:52am	20089804	asdf	luismary	asdf
7	09:52am	20089805	asdf	mary	asf
8	09:52am	20089806	asdf	george	sadf
9	09:52am	20089807	sadf	Anna	sadf
10	09:52am	20089808	sad	mike	sad
11	09:52am	20089809	asdf	viol	asd
12	09:52am	20089810	asd	gera	dsa
13
14	ID#	First Name	Last Name
15	20089803	joe	zucca
Foglio1

Cell Formulas
Range	Formula
B15	=INDEX(D$2:D$12,MATCH($A$15,$B$2:$B$12,0))

 

[sindel]

Thank you for trying to help me. I've learned a great deal trying to work through this.

I was able to get the sheet working with these formulas, just in case anyone has a similar issue:

First name formula (goes in cell D2 of first sheet).
=VLOOKUP(B:B,Names!A2:C6421,2,FALSE)

Last name formula (goes in cell E2 of first sheet).
=VLOOKUP(B:B,Names!A2:C6421,3,FALSE)

For other newbies, B;B refers to the column on my first sheet where the students would input their number. Names!A2:C6421 refers to my second sheet and all the data to be looked through. 2 is the column where the kids' first names can be found on that second sheet, and FALSE means I want an exact student ID number match.

 

[GerryZ]

Hello!
with Excel ther's Always more than one way to do the same thing
my INDEX and MATCH formula is another approch

 

[bonescoster]

If you are interested there is also a way to have the name shown in the same cell instead of in two separate cells. For example: if you wanted the full name to appear in Sheet One in the D2 where the first name appears you could combine your two VLookUp statements and it would look like this:

=VLOOKUP(B:B,Names!A2:C6421,2,FALSE)&" "&VLOOKUP(B:B,Names!A2:C6421,3,FALSE)

I would make the suggestion to change the area you are referencing in the "Names" sheet to be fixed ($A$2:$C$6421) so that if you copy of fill this formula on the Frist sheet it does not mess up what you are doing by moving your lookup array. OR if you want it open to add more students in the future, then you could do the full columns (with the assumption that rows in Columns A through C will only be used for student info). This would be as simple as this:

=VLOOKUP(B:B,Names!A:C,2,FALSE)&" "&VLOOKUP(B:B,Names!A:C,3,FALSE)

Hope this helps.