Lookup
 

I'm trying to read employee's records, and I have lots of Bobs and others,
but different last names on the following column. The question is: how do I
make it read the next column to match the last name to extract the
information for that row? I can't just use the second column because there
some same last names.

This is what I have right now, where $A1 is the first name, and naturally
the last name is B1.

=VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0)

Thanks for your help.

Lookup
 

One way:

Try this array formula** :

=INDEX('Addresses-Main Data Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main Data
Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If the data to be returned is numeric and the names (first last) are listed
only once in the lookup sheet we can use a less complicated non-array
formula.

Biff

"Claudio Funes" wrote in message
...
I'm trying to read employee's records, and I have lots of Bobs and others,
but different last names on the following column. The question is: how do
I
make it read the next column to match the last name to extract the
information for that row? I can't just use the second column because there
some same last names.

This is what I have right now, where $A1 is the first name, and naturally
the last name is B1.

=VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0)

Thanks for your help.

Lookup
 

It's not working.
And the information being pulled is alpha-numeric, and the names are listed
only once.
Thanks for your help.

"T. Valko" wrote:

One way:

Try this array formula** :

=INDEX('Addresses-Main Data Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main Data
Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If the data to be returned is numeric and the names (first last) are listed
only once in the lookup sheet we can use a less complicated non-array
formula.

Biff

"Claudio Funes" wrote in message
...
I'm trying to read employee's records, and I have lots of Bobs and others,
but different last names on the following column. The question is: how do
I
make it read the next column to match the last name to extract the
information for that row? I can't just use the second column because there
some same last names.

This is what I have right now, where $A1 is the first name, and naturally
the last name is B1.

=VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0)

Thanks for your help.

Lookup
 

What does "it's not working mean" ?

Getting an error? Incorrect result?

If you have:

A1 = Bob
B1 = Smith

And on sheet Addresses-Main Data Sheet:

Column A = first names
Column B = last names

That formula will work. Did you enter the formula as an array?

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Claudio Funes" wrote in message
...
It's not working.
And the information being pulled is alpha-numeric, and the names are
listed
only once.
Thanks for your help.

"T. Valko" wrote:

One way:

Try this array formula** :

=INDEX('Addresses-Main Data Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main
Data
Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If the data to be returned is numeric and the names (first last) are
listed
only once in the lookup sheet we can use a less complicated non-array
formula.

Biff

"Claudio Funes" wrote in message
...
I'm trying to read employee's records, and I have lots of Bobs and
others,
but different last names on the following column. The question is: how
do
I
make it read the next column to match the last name to extract the
information for that row? I can't just use the second column because
there
some same last names.

This is what I have right now, where $A1 is the first name, and
naturally
the last name is B1.

=VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0)

Thanks for your help.

Lookup
 

It gives me a #N/A result. And yes, I entered it as an array.



"T. Valko" wrote:

What does "it's not working mean" ?

Getting an error? Incorrect result?

If you have: This is correct.

A1 = Bob
B1 = Smith

And on sheet Addresses-Main Data Sheet:

Column A = first names
Column B = last names

That formula will work. Did you enter the formula as an array?

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Claudio Funes" wrote in message
...
It's not working.
And the information being pulled is alpha-numeric, and the names are
listed
only once.
Thanks for your help.

"T. Valko" wrote:

One way:

Try this array formula** :

=INDEX('Addresses-Main Data Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main
Data
Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If the data to be returned is numeric and the names (first last) are
listed
only once in the lookup sheet we can use a less complicated non-array
formula.

Biff

"Claudio Funes" wrote in message
...
I'm trying to read employee's records, and I have lots of Bobs and
others,
but different last names on the following column. The question is: how
do
I
make it read the next column to match the last name to extract the
information for that row? I can't just use the second column because
there
some same last names.

This is what I have right now, where $A1 is the first name, and
naturally
the last name is B1.

=VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0)

Thanks for your help.

Lookup
 

Here's a small sample file that demonstrates this:

sample_lookup1.xls 14kb

http://cjoint.com/?geh0BIqI5V

As you'll see, the formula does work. If you're having problems I would
check the data. Your lookup values may have unseen characters like
leading/trailing spaces or other unseen characters that is causing them not
to match with the lookup table. Or, the names on the lookup table may have
the unseen characters.

Biff

"Claudio Funes" wrote in message
...
It gives me a #N/A result. And yes, I entered it as an array.



"T. Valko" wrote:

What does "it's not working mean" ?

Getting an error? Incorrect result?

If you have: This is correct.

A1 = Bob
B1 = Smith

And on sheet Addresses-Main Data Sheet:

Column A = first names
Column B = last names

That formula will work. Did you enter the formula as an array?

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Claudio Funes" wrote in message
...
It's not working.
And the information being pulled is alpha-numeric, and the names are
listed
only once.
Thanks for your help.

"T. Valko" wrote:

One way:

Try this array formula** :

=INDEX('Addresses-Main Data
Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main
Data
Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If the data to be returned is numeric and the names (first last) are
listed
only once in the lookup sheet we can use a less complicated non-array
formula.

Biff

"Claudio Funes" wrote in
message
...
I'm trying to read employee's records, and I have lots of Bobs and
others,
but different last names on the following column. The question is:
how
do
I
make it read the next column to match the last name to extract the
information for that row? I can't just use the second column because
there
some same last names.

This is what I have right now, where $A1 is the first name, and
naturally
the last name is B1.

=VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0)

Thanks for your help.

Lookup
 

It works great, thank you very much.


"T. Valko" wrote:

Here's a small sample file that demonstrates this:

sample_lookup1.xls 14kb

http://cjoint.com/?geh0BIqI5V

As you'll see, the formula does work. If you're having problems I would
check the data. Your lookup values may have unseen characters like
leading/trailing spaces or other unseen characters that is causing them not
to match with the lookup table. Or, the names on the lookup table may have
the unseen characters.

Biff

"Claudio Funes" wrote in message
...
It gives me a #N/A result. And yes, I entered it as an array.



"T. Valko" wrote:

What does "it's not working mean" ?

Getting an error? Incorrect result?

If you have: This is correct.

A1 = Bob
B1 = Smith

And on sheet Addresses-Main Data Sheet:

Column A = first names
Column B = last names

That formula will work. Did you enter the formula as an array?

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Claudio Funes" wrote in message
...
It's not working.
And the information being pulled is alpha-numeric, and the names are
listed
only once.
Thanks for your help.

"T. Valko" wrote:

One way:

Try this array formula** :

=INDEX('Addresses-Main Data
Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main
Data
Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If the data to be returned is numeric and the names (first last) are
listed
only once in the lookup sheet we can use a less complicated non-array
formula.

Biff

"Claudio Funes" wrote in
message
...
I'm trying to read employee's records, and I have lots of Bobs and
others,
but different last names on the following column. The question is:
how
do
I
make it read the next column to match the last name to extract the
information for that row? I can't just use the second column because
there
some same last names.

This is what I have right now, where $A1 is the first name, and
naturally
the last name is B1.

=VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0)

Thanks for your help.

Lookup
 

You're welcome. Thanks for the feedback!

Biff

"Claudio Funes" wrote in message
...
It works great, thank you very much.


"T. Valko" wrote:

Here's a small sample file that demonstrates this:

sample_lookup1.xls 14kb

http://cjoint.com/?geh0BIqI5V

As you'll see, the formula does work. If you're having problems I would
check the data. Your lookup values may have unseen characters like
leading/trailing spaces or other unseen characters that is causing them
not
to match with the lookup table. Or, the names on the lookup table may
have
the unseen characters.

Biff

"Claudio Funes" wrote in message
...
It gives me a #N/A result. And yes, I entered it as an array.



"T. Valko" wrote:

What does "it's not working mean" ?

Getting an error? Incorrect result?

If you have: This is correct.

A1 = Bob
B1 = Smith

And on sheet Addresses-Main Data Sheet:

Column A = first names
Column B = last names

That formula will work. Did you enter the formula as an array?

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Claudio Funes" wrote in
message
...
It's not working.
And the information being pulled is alpha-numeric, and the names are
listed
only once.
Thanks for your help.

"T. Valko" wrote:

One way:

Try this array formula** :

=INDEX('Addresses-Main Data
Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main
Data
Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If the data to be returned is numeric and the names (first last)
are
listed
only once in the lookup sheet we can use a less complicated
non-array
formula.

Biff

"Claudio Funes" wrote in
message
...
I'm trying to read employee's records, and I have lots of Bobs
and
others,
but different last names on the following column. The question
is:
how
do
I
make it read the next column to match the last name to extract
the
information for that row? I can't just use the second column
because
there
some same last names.

This is what I have right now, where $A1 is the first name, and
naturally
the last name is B1.

=VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0)

Thanks for your help.

Lookup
 

Just one last question, what does the 3 in your formula represent?
In my VLOOKUP string it represented the column from which I wanted to pull
data from, in yours I'm not sure.
Thanks


"T. Valko" wrote:

Here's a small sample file that demonstrates this:

sample_lookup1.xls 14kb

http://cjoint.com/?geh0BIqI5V

As you'll see, the formula does work. If you're having problems I would
check the data. Your lookup values may have unseen characters like
leading/trailing spaces or other unseen characters that is causing them
not
to match with the lookup table. Or, the names on the lookup table may
have
the unseen characters.

Biff

"Claudio Funes" wrote in message
...
It gives me a #N/A result. And yes, I entered it as an array.



"T. Valko" wrote:

What does "it's not working mean" ?

Getting an error? Incorrect result?

If you have: This is correct.

A1 = Bob
B1 = Smith

And on sheet Addresses-Main Data Sheet:

Column A = first names
Column B = last names

That formula will work. Did you enter the formula as an array?

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Claudio Funes" wrote in
message
...
It's not working.
And the information being pulled is alpha-numeric, and the names are
listed
only once.
Thanks for your help.

"T. Valko" wrote:

One way:

Try this array formula** :

=INDEX('Addresses-Main Data
Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main
Data
Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If the data to be returned is numeric and the names (first last)
are
listed
only once in the lookup sheet we can use a less complicated
non-array
formula.

Biff

"Claudio Funes" wrote in
message
...
I'm trying to read employee's records, and I have lots of Bobs
and
others,
but different last names on the following column. The question
is:
how
do
I
make it read the next column to match the last name to extract
the
information for that row? I can't just use the second column
because
there
some same last names.

This is what I have right now, where $A1 is the first name, and
naturally
the last name is B1.

=VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0)

Thanks for your help.

Lookup
 

It means the same thing, the column number. I used that particular formula
specifically so that, if needed, you could use it to pull other data from
the same table by simply changing the column number.

Biff

"Claudio Funes" wrote in message
...
Just one last question, what does the 3 in your formula represent?
In my VLOOKUP string it represented the column from which I wanted to pull
data from, in yours I'm not sure.
Thanks


"T. Valko" wrote:

Here's a small sample file that demonstrates this:

sample_lookup1.xls 14kb

http://cjoint.com/?geh0BIqI5V

As you'll see, the formula does work. If you're having problems I
would
check the data. Your lookup values may have unseen characters like
leading/trailing spaces or other unseen characters that is causing
them
not
to match with the lookup table. Or, the names on the lookup table may
have
the unseen characters.

Biff

"Claudio Funes" wrote in
message
...
It gives me a #N/A result. And yes, I entered it as an array.



"T. Valko" wrote:

What does "it's not working mean" ?

Getting an error? Incorrect result?

If you have: This is correct.

A1 = Bob
B1 = Smith

And on sheet Addresses-Main Data Sheet:

Column A = first names
Column B = last names

That formula will work. Did you enter the formula as an array?

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Claudio Funes" wrote in
message
...
It's not working.
And the information being pulled is alpha-numeric, and the names
are
listed
only once.
Thanks for your help.

"T. Valko" wrote:

One way:

Try this array formula** :

=INDEX('Addresses-Main Data
Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main
Data
Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3)

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER)

If the data to be returned is numeric and the names (first last)
are
listed
only once in the lookup sheet we can use a less complicated
non-array
formula.

Biff

"Claudio Funes" wrote
in
message
...
I'm trying to read employee's records, and I have lots of Bobs
and
others,
but different last names on the following column. The question
is:
how
do
I
make it read the next column to match the last name to extract
the
information for that row? I can't just use the second column
because
there
some same last names.

This is what I have right now, where $A1 is the first name,
and
naturally
the last name is B1.

=VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0)

Thanks for your help.

Lookup
 

Yes, I had figured it out, but thank you very much, you've been a great help.
Regards,
Claudio

"T. Valko" wrote:

It means the same thing, the column number. I used that particular formula
specifically so that, if needed, you could use it to pull other data from
the same table by simply changing the column number.

Biff

"Claudio Funes" wrote in message
...
Just one last question, what does the 3 in your formula represent?
In my VLOOKUP string it represented the column from which I wanted to pull
data from, in yours I'm not sure.
Thanks


"T. Valko" wrote:

Here's a small sample file that demonstrates this:

sample_lookup1.xls 14kb

http://cjoint.com/?geh0BIqI5V

As you'll see, the formula does work. If you're having problems I
would
check the data. Your lookup values may have unseen characters like
leading/trailing spaces or other unseen characters that is causing
them
not
to match with the lookup table. Or, the names on the lookup table may
have
the unseen characters.

Biff

"Claudio Funes" wrote in
message
...
It gives me a #N/A result. And yes, I entered it as an array.



"T. Valko" wrote:

What does "it's not working mean" ?

Getting an error? Incorrect result?

If you have: This is correct.

A1 = Bob
B1 = Smith

And on sheet Addresses-Main Data Sheet:

Column A = first names
Column B = last names

That formula will work. Did you enter the formula as an array?

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Claudio Funes" wrote in
message
...
It's not working.
And the information being pulled is alpha-numeric, and the names
are
listed
only once.
Thanks for your help.

"T. Valko" wrote:

One way:

Try this array formula** :

=INDEX('Addresses-Main Data
Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main
Data
Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3)

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER)

If the data to be returned is numeric and the names (first last)
are
listed
only once in the lookup sheet we can use a less complicated
non-array
formula.

Biff

"Claudio Funes" wrote
in
message
...
I'm trying to read employee's records, and I have lots of Bobs
and
others,
but different last names on the following column. The question
is:
how
do
I
make it read the next column to match the last name to extract
the
information for that row? I can't just use the second column
because
there
some same last names.

This is what I have right now, where $A1 is the first name,
and
naturally
the last name is B1.

=VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0)

Thanks for your help.

Lookup
 

You're welcome!

Biff

"Claudio Funes" wrote in message
...
Yes, I had figured it out, but thank you very much, you've been a great
help.
Regards,
Claudio

"T. Valko" wrote:

It means the same thing, the column number. I used that particular
formula
specifically so that, if needed, you could use it to pull other data from
the same table by simply changing the column number.

Biff

"Claudio Funes" wrote in message
...
Just one last question, what does the 3 in your formula represent?
In my VLOOKUP string it represented the column from which I wanted to
pull
data from, in yours I'm not sure.
Thanks


"T. Valko" wrote:

Here's a small sample file that demonstrates this:

sample_lookup1.xls 14kb

http://cjoint.com/?geh0BIqI5V

As you'll see, the formula does work. If you're having problems I
would
check the data. Your lookup values may have unseen characters like
leading/trailing spaces or other unseen characters that is causing
them
not
to match with the lookup table. Or, the names on the lookup table
may
have
the unseen characters.

Biff

"Claudio Funes" wrote in
message
...
It gives me a #N/A result. And yes, I entered it as an array.



"T. Valko" wrote:

What does "it's not working mean" ?

Getting an error? Incorrect result?

If you have: This is correct.

A1 = Bob
B1 = Smith

And on sheet Addresses-Main Data Sheet:

Column A = first names
Column B = last names

That formula will work. Did you enter the formula as an array?

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

"Claudio Funes" wrote
in
message
...
It's not working.
And the information being pulled is alpha-numeric, and the
names
are
listed
only once.
Thanks for your help.

"T. Valko" wrote:

One way:

Try this array formula** :

=INDEX('Addresses-Main Data
Sheet'!A1:K1000,MATCH(A1&B1,'Addresses-Main
Data
Sheet'!A1:A1000&'Addresses-Main Data Sheet'!B1:B1000,0),3)

** array formulas need to be entered using the key
combination
of
CTRL,SHIFT,ENTER (not just ENTER)

If the data to be returned is numeric and the names (first
last)
are
listed
only once in the lookup sheet we can use a less complicated
non-array
formula.

Biff

"Claudio Funes"
wrote
in
message
...
I'm trying to read employee's records, and I have lots of
Bobs
and
others,
but different last names on the following column. The
question
is:
how
do
I
make it read the next column to match the last name to
extract
the
information for that row? I can't just use the second
column
because
there
some same last names.

This is what I have right now, where $A1 is the first name,
and
naturally
the last name is B1.

=VLOOKUP($A1,'Adresses-Main Data Sheet'!$A$1:$K$1000,3,0)

Thanks for your help.