VLOOKUP rookie question

[Brown]

Good Afternoon,
I am trying to assist someone with a VLOOKUP issue. I have read the directions and watched a couple of videos and I just cannot translate it to this spread sheet.
This person wants to type in an ID number and have it return last name, first name, and grade into a different sheet in the workbook.

Looking up column A on this sheet

need to have column C, D, E filled once column B has a number that matches column in the first sheet.

is this possible?

Thank you in advance for your assistance.
Brown

 

[Asbestos_Jen]

If your source table (Table1) is on sheet1 and your output is on sheet2, it would look like this:

Book1
	A	B	C	D
1	Student ID	First Name	Last Name	Grade Level
2	1111111	Tom	Cat	9
3	1150407	Bob	Marley	9
4	1201082	Alice	Cooper	9
5	1201753	Jane	Doe	9
6	1203005	Sam	Roberts	9
Sheet1

Book1
	B	C	D	E	F
2	#	CCID	First Name	Last Name	Grade Level
3	1	1201753	Jane	Doe	9
4	2	1111111	Tom	Cat	9
5	3	1203005	Sam	Roberts	9
6	4	1150407	Bob	Marley	9
7	5	1201082	Alice	Cooper	9
8	6
9	7
10	8
11	9
12	10
13	11
14	12
Sheet2

Cell Formulas
Range	Formula
D3:D14	D3	=IFERROR(VLOOKUP($C3,Table1[#All],2),"")
E3:E14	E3	=IFERROR(VLOOKUP($C3,Table1[#All],3),"")
F3:F14	F3	=IFERROR(VLOOKUP($C3,Table1[#All],4),"")

 

[Brown]

Thank you, on the output sheet if I type the CCID then the names and grade will populate?

 

[Asbestos_Jen]

Yes, if they match an entry in the source table.

 

[AhoyNC]

If they have Excel 365 another option would be the FILTER function.
In the example below just enter the formula in C3 and drag down. It will fill out all 3 columns going across.

Sheet2

Book1
	A	B	C	D	E
1
2	#	Student ID	First Name	Last Name	Grade Level
3	1	1150407	Bob	Marley	10
4	2	1201753	Jane	Doe	11
5	3	1201082	Alice	Cooper	9
Sheet2

Cell Formulas
Range	Formula
C3:E5	C3	=FILTER(Sheet1!$B$2:$D$6,Sheet1!$A$2:$A$6=B3,"No Match")
Dynamic array formulas.

Sheet1

Book1
	A	B	C	D
1	Student ID	First Name	Last Name	Grade Level
2	1111111	Tom	Cat	9
3	1150407	Bob	Marley	10
4	1201082	Alice	Cooper	9
5	1201753	Jane	Doe	11
6	1203005	Sam	Roberts	10
Sheet1

 

[Brown]

If they have Excel 365 another option would be the FILTER function.
In the example below just enter the formula in C3 and drag down. It will fill out all 3 columns going across.

Sheet2

Book1
	A	B	C	D	E
1
2	#	Student ID	First Name	Last Name	Grade Level
3	1	1150407	Bob	Marley	10
4	2	1201753	Jane	Doe	11
5	3	1201082	Alice	Cooper	9
Sheet2

Cell Formulas
Range	Formula
C3:E5	C3	=FILTER(Sheet1!$B$2:$D$6,Sheet1!$A$2:$A$6=B3,"No Match")
Dynamic array formulas.

Sheet1

Book1
	A	B	C	D
1	Student ID	First Name	Last Name	Grade Level
2	1111111	Tom	Cat	9
3	1150407	Bob	Marley	10
4	1201082	Alice	Cooper	9
5	1201753	Jane	Doe	11
6	1203005	Sam	Roberts	10
Sheet1

Thank you all for the help.
Have a great day

 

[Michael M]

For first name on sheet 1

=VLOOKUP(Sheet1!A1,Sheet1!A1:D3,3,0)

change ranges to suit

 

[alansidman]

from your duplicate post

Book2
	A	B	C	D	E	F
1	Student ID	First Name	Last Name	Grade Level		CCID
2	1049384	Bill	Smith	12		1049384
3	1055550	Jim	Black	12		1055550
4	1062231	Bob	Smalls	12		1062231
5	1063728	Susy	Que	12		1063728
6	1065545	Tom	Jerry	12		1065545
7	1066206	Sarah	Connor	12		1066206
8
9	CCID	First Name	Last Name	Grade Level
10	1049384	Bill	Smith	12
11	1055550	Jim	Black	12
12	1062231	Bob	Smalls	12
13	1063728	Susy	Que	12
14	1065545	Tom	Jerry	12
15	1066206	Sarah	Connor	12
Sheet1

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ = Table.NestedJoin(T2, {"CCID"},T1, {"Student ID"}, "T3", JoinKind.FullOuter),
    #"Expanded T3" = Table.ExpandTableColumn(MQ, "T3", {"First Name", "Last Name", "Grade Level"}, {"First Name", "Last Name", "Grade Level"})

in
    #"Expanded T3"

 

[Brown]

Thank you all! I am working on it this morning. I am finally able to get the personal data (to basically copy over to the second sheet) but i have not been able to type in the ID number in the first column and have the rest of the data populate?

 

[Brown]

Would Windows 11 be causing me any issues with this?
I just had my machine updated last week, and I am still working through some of the changes.