Vlookup function question

[SL]

I am using two spreadsheets. Both spreadsheets have last name and first
names of students from our school. I am trying to lookup the student numbers
from one spreadsheet and populate to the other. I can do it just fine except
when there are duplicate last names. Does anyone know how to lookup more
than just one column on duplicate last names. Example, Williams (last name)
tons of them, I need Excel to look at Williams and first name (Jane) before
returning the student number.

Thanks. SL

[malik641]

Here's one way, though it's not the best:

=INDEX(Sheet1!A1:C10,MATCH(A1,Sheet1!A1:A10,0)+MATCH(B1,Sheet1!B1:B10,0)-1,3)

In sheet one:
Last names are in column A
First names are in column B
Student numbers are in column C

In sheet2:
Last name is in A1
First name is in B1
Formula is in C1


...Still working on it. But let me know how that one works for ya.

[Biff]

Hi!

Maybe this:

=SUMPRODUCT(--(A1:A100="last_name"),--(B1:B100="first_name"),C1:C100)

OR....

Redo your lookup table so that the first column includes both
lastname,firstname

OR....

Assume lookup table is such:

Column A = lastname
Column B = firstname
Column C = student number

D1 = Williams
E1 = Venus

=INDEX(C1:C100,MATCH(D1&E1,A1:A100&B1:B100,0))

Array entered using the key combo of CTRL,SHIFT,ENTER.

Biff

"SL" <[email protected]> wrote in message
news:[email protected]...
>I am using two spreadsheets. Both spreadsheets have last name and first
> names of students from our school. I am trying to lookup the student
> numbers
> from one spreadsheet and populate to the other. I can do it just fine
> except
> when there are duplicate last names. Does anyone know how to lookup more
> than just one column on duplicate last names. Example, Williams (last
> name)
> tons of them, I need Excel to look at Williams and first name (Jane)
> before
> returning the student number.
>
> Thanks. SL

[Bob Phillips]

You can do it with INDEX/MATCH like so

=INDEX(Sheet2!A1:A1000,MATCH(B1&C1,Sheet2!B1:B1000&Sheet2!C1:C1000,0))

obviously adjust the ranges to suit your data

--
HTH

Bob Phillips

"SL" <[email protected]> wrote in message
news:[email protected]...
> I am using two spreadsheets. Both spreadsheets have last name and first
> names of students from our school. I am trying to lookup the student
numbers
> from one spreadsheet and populate to the other. I can do it just fine
except
> when there are duplicate last names. Does anyone know how to lookup more
> than just one column on duplicate last names. Example, Williams (last
name)
> tons of them, I need Excel to look at Williams and first name (Jane)
before
> returning the student number.
>
> Thanks. SL

[malik641]

I don't know if anyone noticed but that INDEX function:

=INDEX(Sheet1!C1:C10,MATCH(A1&B1,Sheet1!A1:A10&Sheet1!B1:B10,0))
Entered with Ctrl+Shift+Enter

Actually gives you the student number AFTER the one you want (at least that's what's happening to me).

It could be corrected by:

=INDEX(Sheet1!C1:C10,MATCH(A1&B1,Sheet1!A1:A10&Sheet1!B1:B10,0)-1)
Entered with Ctrl+Shift+Enter

[malik641]

Oops! Nevermind, my fault.

Didn't realize I left the Exact match out of the MATCH function in my spreadsheeet....der

Sorry about that

[SL]

Biff,

Thank you for your reply. I tried the index but I got #value! error. In
your example:
lookup table:
column A = last name
Column B = first name
Column C = Student Id

then you used D1 = williams
E1 = Venus
Here is where I was confused. Venus williams say would be the student I am
looking up to find the student id. So my wks#2 has a list of last name and
first name say :
Column A = Last name (ex:williams)
Column B = first name (ex:Venus)

I put the function in wks#2 column C1
=index([wks#1]C1:C100,match(A1&B1,[wk#1]A1:A100&B1:B100,0))

I got a #value!

What went wrong??? Am I messing up the function?

Thanks for your help Biff
SL

"Biff" wrote:

> Hi!
>
> Maybe this:
>
> =SUMPRODUCT(--(A1:A100="last_name"),--(B1:B100="first_name"),C1:C100)
>
> OR....
>
> Redo your lookup table so that the first column includes both
> lastname,firstname
>
> OR....
>
> Assume lookup table is such:
>
> Column A = lastname
> Column B = firstname
> Column C = student number
>
> D1 = Williams
> E1 = Venus
>
> =INDEX(C1:C100,MATCH(D1&E1,A1:A100&B1:B100,0))
>
> Array entered using the key combo of CTRL,SHIFT,ENTER.
>
> Biff
>
> "SL" <[email protected]> wrote in message
> news:[email protected]...
> >I am using two spreadsheets. Both spreadsheets have last name and first
> > names of students from our school. I am trying to lookup the student
> > numbers
> > from one spreadsheet and populate to the other. I can do it just fine
> > except
> > when there are duplicate last names. Does anyone know how to lookup more
> > than just one column on duplicate last names. Example, Williams (last
> > name)
> > tons of them, I need Excel to look at Williams and first name (Jane)
> > before
> > returning the student number.
> >
> > Thanks. SL
>
>
>

[SL]

Malik641:

I tried this:
=INDEX([WVE1_passwords2.xls]WVE1_USERS!$A$1:$C$424,MATCH(A7,[WVE1_passwords2.xls]WVE1_USERS!$A$1:$A$424,0)+MATCH(B7,[WVE1_passwords2.xls]WVE1_USERS!$B$1:$B$424,0))-13

got an #N/A error--what does that mean? It would not allow me to end my
function in -1,3) like you suggested. It changed it to -13???

the wve1_passwords2 is the lookup table with the lastname (A), firstname
(B), and student ids (C) in it. and the wve1_users is the sheet name.

Thanks for all your help. Any other ideas?? I know it is close but I am
just missing something maybe syntax?

shaunna

"malik641" wrote:

>
> Here's one way, though it's not the best:
>
> =INDEX(Sheet1!A1:C10,MATCH(A1,Sheet1!A1:A10,0)+MATCH(B1,Sheet1!B1:B10,0)-1,3)
>
> In sheet one:
> Last names are in column A
> First names are in column B
> Student numbers are in column C
>
> In sheet2:
> Last name is in A1
> First name is in B1
> Formula is in C1
>
>
> ...Still working on it. But let me know how that one works for ya.
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=468318
>
>

[Biff]

Hi!

> I put the function in wks#2 column C1
> =index([wks#1]C1:C100,match(A1&B1,[wk#1]A1:A100&B1:B100,0))
>
> I got a #value!

Type this formula:

=INDEX(Sheet1!C1:C100,MATCH(A1&B1,Sheet1!A1:A100&Sheet1!B1:B100,0))

Replace Sheet1 with your actual sheet name.

Now, when you're done typing the formula INSTEAD of hitting the enter key
hold down the CTRL key and the SHIFT key then hit ENTER. When done properly
Excel will place squiggly braces { } around the formula. The braces denote
an array formula. You cannot just type the braces, you MUST use the key
combo of CTRL,SHIFT,ENTER.

Biff

"SL" <[email protected]> wrote in message
news:[email protected]...
> Biff,
>
> Thank you for your reply. I tried the index but I got #value! error. In
> your example:
> lookup table:
> column A = last name
> Column B = first name
> Column C = Student Id
>
> then you used D1 = williams
> E1 = Venus
> Here is where I was confused. Venus williams say would be the student I
> am
> looking up to find the student id. So my wks#2 has a list of last name
> and
> first name say :
> Column A = Last name (ex:williams)
> Column B = first name (ex:Venus)
>
> I put the function in wks#2 column C1
> =index([wks#1]C1:C100,match(A1&B1,[wk#1]A1:A100&B1:B100,0))
>
> I got a #value!
>
> What went wrong??? Am I messing up the function?
>
> Thanks for your help Biff
> SL
>
> "Biff" wrote:
>
>> Hi!
>>
>> Maybe this:
>>
>> =SUMPRODUCT(--(A1:A100="last_name"),--(B1:B100="first_name"),C1:C100)
>>
>> OR....
>>
>> Redo your lookup table so that the first column includes both
>> lastname,firstname
>>
>> OR....
>>
>> Assume lookup table is such:
>>
>> Column A = lastname
>> Column B = firstname
>> Column C = student number
>>
>> D1 = Williams
>> E1 = Venus
>>
>> =INDEX(C1:C100,MATCH(D1&E1,A1:A100&B1:B100,0))
>>
>> Array entered using the key combo of CTRL,SHIFT,ENTER.
>>
>> Biff
>>
>> "SL" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am using two spreadsheets. Both spreadsheets have last name and first
>> > names of students from our school. I am trying to lookup the student
>> > numbers
>> > from one spreadsheet and populate to the other. I can do it just fine
>> > except
>> > when there are duplicate last names. Does anyone know how to lookup
>> > more
>> > than just one column on duplicate last names. Example, Williams (last
>> > name)
>> > tons of them, I need Excel to look at Williams and first name (Jane)
>> > before
>> > returning the student number.
>> >
>> > Thanks. SL
>>
>>
>>

[kk]

=INDEX([WVE1_passwords2.xls]WVE1_USERS!$A$1:$C$424,MATCH(A7,[WVE1_passwords2.xls]WVE1_USERS!$A$1:$A$424,0)+MATCH(B7,[WVE1_passwords2.xls]WVE1_USERS!$B$1:$B$424,0)-1,3)


"SL" <[email protected]> wrote in message
news:[email protected]...
Malik641:

I tried this:
=INDEX([WVE1_passwords2.xls]WVE1_USERS!$A$1:$C$424,MATCH(A7,[WVE1_passwords2.xls]WVE1_USERS!$A$1:$A$424,0)+MATCH(B7,[WVE1_passwords2.xls]WVE1_USERS!$B$1:$B$424,0))-13

got an #N/A error--what does that mean? It would not allow me to end my
function in -1,3) like you suggested. It changed it to -13???

the wve1_passwords2 is the lookup table with the lastname (A), firstname
(B), and student ids (C) in it. and the wve1_users is the sheet name.

Thanks for all your help. Any other ideas?? I know it is close but I am
just missing something maybe syntax?

shaunna

"malik641" wrote:

>
> Here's one way, though it's not the best:
>
> =INDEX(Sheet1!A1:C10,MATCH(A1,Sheet1!A1:A10,0)+MATCH(B1,Sheet1!B1:B10,0)-1,3)
>
> In sheet one:
> Last names are in column A
> First names are in column B
> Student numbers are in column C
>
> In sheet2:
> Last name is in A1
> First name is in B1
> Formula is in C1
>
>
> ...Still working on it. But let me know how that one works for ya.
>
>
> --
> malik641
>
>
> ------------------------------------------------------------------------
> malik641's Profile:
> http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=468318
>
>

[SL]

Dear KK,

Excel accepted the formula you listed below but I get a #N/A. Any possible
problems I can research? What does the 0)-1,3 at the end of the function
mean?

thanks for the help
SL

"kk" wrote:

> =INDEX([WVE1_passwords2.xls]WVE1_USERS!$A$1:$C$424,MATCH(A7,[WVE1_passwords2.xls]WVE1_USERS!$A$1:$A$424,0)+MATCH(B7,[WVE1_passwords2.xls]WVE1_USERS!$B$1:$B$424,0)-1,3)
>
>
> "SL" <[email protected]> wrote in message
> news:[email protected]...
> Malik641:
>
> I tried this:
> =INDEX([WVE1_passwords2.xls]WVE1_USERS!$A$1:$C$424,MATCH(A7,[WVE1_passwords2.xls]WVE1_USERS!$A$1:$A$424,0)+MATCH(B7,[WVE1_passwords2.xls]WVE1_USERS!$B$1:$B$424,0))-13
>
> got an #N/A error--what does that mean? It would not allow me to end my
> function in -1,3) like you suggested. It changed it to -13???
>
> the wve1_passwords2 is the lookup table with the lastname (A), firstname
> (B), and student ids (C) in it. and the wve1_users is the sheet name.
>
> Thanks for all your help. Any other ideas?? I know it is close but I am
> just missing something maybe syntax?
>
> shaunna
>
> "malik641" wrote:
>
> >
> > Here's one way, though it's not the best:
> >
> > =INDEX(Sheet1!A1:C10,MATCH(A1,Sheet1!A1:A10,0)+MATCH(B1,Sheet1!B1:B10,0)-1,3)
> >
> > In sheet one:
> > Last names are in column A
> > First names are in column B
> > Student numbers are in column C
> >
> > In sheet2:
> > Last name is in A1
> > First name is in B1
> > Formula is in C1
> >
> >
> > ...Still working on it. But let me know how that one works for ya.
> >
> >
> > --
> > malik641
> >
> >
> > ------------------------------------------------------------------------
> > malik641's Profile:
> > http://www.excelforum.com/member.php...o&userid=24190
> > View this thread: http://www.excelforum.com/showthread...hreadid=468318
> >
> >
>
>
>

[Ingrid]

Try this:

Add to both spreadsheet a colomn and combine the first and last name together (=A1&B1). If you need more information just add them to your combination. You can then use your VLOOKUP again. Works like a charm.

[SL]

Ingrid,

Thank you for the tip. It did work. One other question. What is the
function to clear all spaces after the name? Example: JamesSarah# (where #
equals space)

SL

"Ingrid" wrote:

>
> Try this:
>
> Add to both spreadsheet a colomn and combine the first and last name
> together (=A1&B1). If you need more information just add them to your
> combination. You can then use your VLOOKUP again. Works like a charm.
>
>
> --
> Ingrid
> ------------------------------------------------------------------------
> Ingrid's Profile: http://www.excelforum.com/member.php...o&userid=27386
> View this thread: http://www.excelforum.com/showthread...hreadid=468318
>
>

[Chip Pearson]

Use the TRIM function. E.g.,

=TRIM(A1)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"SL" <[email protected]> wrote in message
news:[email protected]...
> Ingrid,
>
> Thank you for the tip. It did work. One other question. What
> is the
> function to clear all spaces after the name? Example:
> JamesSarah# (where #
> equals space)
>
> SL
>
> "Ingrid" wrote:
>
>>
>> Try this:
>>
>> Add to both spreadsheet a colomn and combine the first and
>> last name
>> together (=A1&B1). If you need more information just add them
>> to your
>> combination. You can then use your VLOOKUP again. Works like a
>> charm.
>>
>>
>> --
>> Ingrid
>> ------------------------------------------------------------------------
>> Ingrid's Profile:
>> http://www.excelforum.com/member.php...o&userid=27386
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=468318
>>
>>