VLOOKUP Novice

[Jeff Nelson - Lincoln College]

I'm not even sure if VLOOKUP is what I want to use, but here goes:

We are importing a list of Student ID's, First Names, and Last Names into
Excel. Then, as students show their student ID, the barcode scanner will scan
their Student ID into the system.

We would like it to display the student first name and last name next to the
Student ID number when the ID is scanned.

For example, student shows his ID. Attendance scans the ID with his
hand-held barcode scanner. The ID number (#423423) then displays on the
screen. We would like the student name to appear right along side the ID>

<Scan> ID#: 423423 Simpson, Stan

Any ideas?

Thanks,
Jeff

Office 2007 Product

 

[Sean Timmons]

Sure, assuming the id drops into, say, A2, in B2, type
=VLOOKUP(A2,StudentList!A:C,3,0)&", "&VLOOKUP(A2,StudentList!A:C,2,0)

This will show last, first into cell B2.

Copy the formula down your rows.

This assumes your data is on a tab named StudentList and is in columns A
through C.

 

[Dave Peterson]

After you get the id's into the worksheet, you could use this technique.

Create a new worksheet (call it Table) with a 3 column table.
In column A, put the student id.
In column B, put the first name.
In column C, put the last name.

Then you could use a formula like this (with the ID in A1):

=vlookup(a1,table!a:c,2,false)
(to get the first name)
=vlookup(a1,table!a:c,3,false)
(to get the last name)

Or better:
=if(a1="","",if(isna(vlookup(a1,table!a:c,2,false)),"Missing",
vlookup(a1,table!a:c,2,false)))

(same kind of thing for the last name.

 

[Jeff Nelson - Lincoln College]

Thanks! I'll give it a try!

Sure, assuming the id drops into, say, A2, in B2, type
=VLOOKUP(A2,StudentList!A:C,3,0)&", "&VLOOKUP(A2,StudentList!A:C,2,0)

This will show last, first into cell B2.

Copy the formula down your rows.

This assumes your data is on a tab named StudentList and is in columns A
through C.

 

[Jeff Nelson - Lincoln College]

Thanks Dave.....I'll give this a try...

After you get the id's into the worksheet, you could use this technique.

Create a new worksheet (call it Table) with a 3 column table.
In column A, put the student id.
In column B, put the first name.
In column C, put the last name.

Then you could use a formula like this (with the ID in A1):

=vlookup(a1,table!a:c,2,false)
(to get the first name)
=vlookup(a1,table!a:c,3,false)
(to get the last name)

Or better:
=if(a1="","",if(isna(vlookup(a1,table!a:c,2,false)),"Missing",
vlookup(a1,table!a:c,2,false)))

(same kind of thing for the last name.

 

[Jeff Nelson - Lincoln College]

Sean,
When I typed in the formula you have below, it automatically returns the
last name and the first name of the student in Row 2.

What I want to happen is when I scan the student ID, the ID # will display
on the Excel worksheet in (let's say) A712 (because we have 710 students
listed in the worksheet) and then as soon as the Student ID # pops onto the
worksheet, the column next to the ID# should match the student's name from
the entire list above.

Student ID# Last Name First Name
123 Allen James
124 Bowman PJ
125 Charles Sam

(Scanned ID)
124 Bowman PJ

When I scan ID# 124, "Bowman PJ" should display in the column next to the ID#.

Thanks,
Jeff N.

 

[Jeff Nelson - Lincoln College]

Dave,
When I typed in the formula you have below, it automatically returns the
last name and the first name of the student in Row 2.

What I want to happen is when I scan the student ID, the ID # will display
on the Excel worksheet in (let's say) A712 (because we have 710 students
listed in the worksheet) and then as soon as the Student ID # pops onto the
worksheet, the column next to the ID# should match the student's name from
the entire list above.

Student ID# Last Name First Name
123 Allen James
124 Bowman PJ
125 Charles Sam

(Scanned ID)
124 Bowman PJ

When I scan ID# 124, "Bowman PJ" should display in the column next to the ID#.

Thanks,
Jeff N.

 

[Dave Peterson]

I think you made a mistake--either with your data entry or with your formula.
Take another look.

And I'd return the first and last names to different cells, but ...

You could combine them if you want:

=vlookup(a1,table!a:c,3,false) & " " & vlookup(a1,table!a:c,2,false)

(change the columns to match your table.

 

[Jeff Nelson - Lincoln College]

Student ID last_name first_name
448832 Aaron Ardena
459994 Adams Darius
462790 Addison Irvenna
462131 Agate Jennifer
..
..
..
461657 Wynn Aloni
457277 Ybarra Christopher
462878 Young Michael


* last_name first_name

Key:
* = empty cell a713 awaiting the scanned ID
last_name = cell b713 with the following;
=IF(A1="","",IF(ISNA(VLOOKUP(A1,StudentList!A:C,2,FALSE)),"Missing",VLOOKUP(A1,StudentList!A:C,2,FALSE)))
first_name = cell c713 with the following;
=IF(A1="","",IF(ISNA(VLOOKUP(A1,StudentList!A:C,3,FALSE)),"Missing",VLOOKUP(A1,StudentList!A:C,3,FALSE)))

When I place the scanned ID# into cell a713, I don't get any results in cell
b713 or c713.

As I mentioned in my first post, I am a complete novice, so I have very
little knowledge of how this is done. What am I doing wrong?

Thank you very much for replying!!

Jeff N.

 

[Dave Peterson]

Each of the formulas will have the row in the formula adjusted. So the formula
in B713 will look like:

=IF(A713="","",IF(ISNA(VLOOKUP(A713,StudentList!A:C,2,FALSE)),"Missing",
VLOOKUP(A713,StudentList!A:C,2,FALSE)))

Same for the formula in C713.

I'd plop the formulas in B1 and C1 and drag down as far as you think you'll
need.

If you have headers in B1 and C1, then plop them into B2 and C2, but make sure
you point at A2 in all the spots.

Student ID last_name first_name
448832 Aaron Ardena
459994 Adams Darius
462790 Addison Irvenna
462131 Agate Jennifer
.
.
.
461657 Wynn Aloni
457277 Ybarra Christopher
462878 Young Michael


* last_name first_name

Key:
* = empty cell a713 awaiting the scanned ID
last_name = cell b713 with the following;
=IF(A1="","",IF(ISNA(VLOOKUP(A1,StudentList!A:C,2,FALSE)),"Missing",VLOOKUP(A1,StudentList!A:C,2,FALSE)))
first_name = cell c713 with the following;
=IF(A1="","",IF(ISNA(VLOOKUP(A1,StudentList!A:C,3,FALSE)),"Missing",VLOOKUP(A1,StudentList!A:C,3,FALSE)))

When I place the scanned ID# into cell a713, I don't get any results in cell
b713 or c713.

As I mentioned in my first post, I am a complete novice, so I have very
little knowledge of how this is done. What am I doing wrong?

Thank you very much for replying!!

Jeff N.

 

[Jeff Nelson - Lincoln College]

WORKED PERFECTLY AS YOU STATED!! Thank you very much for your assistance. I
appreciate it very much!

Jeff N.