vlookup.
 

given the data below :

a23 paper 5
b14 lamp 15
a23 paper 2
a23 paper 7

i am using the vlookup and i am having a problem.
i am searching for "paper" ....but each time i am typing for a23 i get
"paper" , so how to do in order to differenciate between the words "papers"
in other words .. if i would like to combine a formula whom let me type
a23 in order to search for "paper" and who has number 5 .....how to do
it????

vlookup.
 

You could try something like this, array-entered (press CTRL+SHIFT+ENTER to
confirm the formula):
=INDEX(C1:C4,MATCH(1,(A1:A4="a23")*(B1:B4="paper") ,0))
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pierre" wrote:
given the data below :

a23 paper 5
b14 lamp 15
a23 paper 2
a23 paper 7

i am using the vlookup and i am having a problem.
i am searching for "paper" ....but each time i am typing for a23 i get
"paper" , so how to do in order to differenciate between the words "papers"
in other words .. if i would like to combine a formula whom let me type
a23 in order to search for "paper" and who has number 5 .....how to do
it????

vlookup.
 

If you want to stay with the vlookup function, the first column needs to be
in ascending order and there can't be any values that repeat in that column.

"Max" wrote:

You could try something like this, array-entered (press CTRL+SHIFT+ENTER to
confirm the formula):
=INDEX(C1:C4,MATCH(1,(A1:A4="a23")*(B1:B4="paper") ,0))
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pierre" wrote:
given the data below :

a23 paper 5
b14 lamp 15
a23 paper 2
a23 paper 7

i am using the vlookup and i am having a problem.
i am searching for "paper" ....but each time i am typing for a23 i get
"paper" , so how to do in order to differenciate between the words "papers"
in other words .. if i would like to combine a formula whom let me type
a23 in order to search for "paper" and who has number 5 .....how to do
it????

vlookup.
 

The first column doesn't need to be sorted for =vlookup() to work. Since it's
going to be an exact match (based on text), the fourth parm should be set to
False (look for an exact match).

And there can be repeats in that first column, but =vlookup() will return the
values from the first match.


Brad Vogt wrote:

If you want to stay with the vlookup function, the first column needs to be
in ascending order and there can't be any values that repeat in that column.

"Max" wrote:

You could try something like this, array-entered (press CTRL+SHIFT+ENTER to
confirm the formula):
=INDEX(C1:C4,MATCH(1,(A1:A4="a23")*(B1:B4="paper") ,0))
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pierre" wrote:
given the data below :

a23 paper 5
b14 lamp 15
a23 paper 2
a23 paper 7

i am using the vlookup and i am having a problem.
i am searching for "paper" ....but each time i am typing for a23 i get
"paper" , so how to do in order to differenciate between the words "papers"
in other words .. if i would like to combine a formula whom let me type
a23 in order to search for "paper" and who has number 5 .....how to do
it????

--

Dave Peterson

vlookup.
 

Put the following in cells A1:B5

How This
When Because
Does Not
What How
Where Why

If you vlookup "When" and return the 2nd column, you will get "How" as the
result, so the first column does need to be in ascending order even if it is
text. Now if you use the Match function and nest it into the vlookup, you
can make it work. Easier to just sort ascending by the first column if
possible though.


"Dave Peterson" wrote:

The first column doesn't need to be sorted for =vlookup() to work. Since it's
going to be an exact match (based on text), the fourth parm should be set to
False (look for an exact match).

And there can be repeats in that first column, but =vlookup() will return the
values from the first match.


Brad Vogt wrote:

If you want to stay with the vlookup function, the first column needs to be
in ascending order and there can't be any values that repeat in that column.

"Max" wrote:

You could try something like this, array-entered (press CTRL+SHIFT+ENTER to
confirm the formula):
=INDEX(C1:C4,MATCH(1,(A1:A4="a23")*(B1:B4="paper") ,0))
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pierre" wrote:
given the data below :

a23 paper 5
b14 lamp 15
a23 paper 2
a23 paper 7

i am using the vlookup and i am having a problem.
i am searching for "paper" ....but each time i am typing for a23 i get
"paper" , so how to do in order to differenciate between the words "papers"
in other words .. if i would like to combine a formula whom let me type
a23 in order to search for "paper" and who has number 5 .....how to do
it????

--

Dave Peterson

vlookup.
 

=vlookup("when",a1:b5,2,false)
returns "This" for me.


Brad Vogt wrote:

Put the following in cells A1:B5

How This
When Because
Does Not
What How
Where Why

If you vlookup "When" and return the 2nd column, you will get "How" as the
result, so the first column does need to be in ascending order even if it is
text. Now if you use the Match function and nest it into the vlookup, you
can make it work. Easier to just sort ascending by the first column if
possible though.

"Dave Peterson" wrote:

The first column doesn't need to be sorted for =vlookup() to work. Since it's
going to be an exact match (based on text), the fourth parm should be set to
False (look for an exact match).

And there can be repeats in that first column, but =vlookup() will return the
values from the first match.


Brad Vogt wrote:

If you want to stay with the vlookup function, the first column needs to be
in ascending order and there can't be any values that repeat in that column.

"Max" wrote:

You could try something like this, array-entered (press CTRL+SHIFT+ENTER to
confirm the formula):
=INDEX(C1:C4,MATCH(1,(A1:A4="a23")*(B1:B4="paper") ,0))
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pierre" wrote:
given the data below :

a23 paper 5
b14 lamp 15
a23 paper 2
a23 paper 7

i am using the vlookup and i am having a problem.
i am searching for "paper" ....but each time i am typing for a23 i get
"paper" , so how to do in order to differenciate between the words "papers"
in other words .. if i would like to combine a formula whom let me type
a23 in order to search for "paper" and who has number 5 .....how to do
it????

--

Dave Peterson


--

Dave Peterson

vlookup.
 

Cool! Thanks!

I think many people will use the default in the function and not add ,false
at the end. I will remember that part for future use.

"Dave Peterson" wrote:

=vlookup("when",a1:b5,2,false)
returns "This" for me.


Brad Vogt wrote:

Put the following in cells A1:B5

How This
When Because
Does Not
What How
Where Why

If you vlookup "When" and return the 2nd column, you will get "How" as the
result, so the first column does need to be in ascending order even if it is
text. Now if you use the Match function and nest it into the vlookup, you
can make it work. Easier to just sort ascending by the first column if
possible though.

"Dave Peterson" wrote:

The first column doesn't need to be sorted for =vlookup() to work. Since it's
going to be an exact match (based on text), the fourth parm should be set to
False (look for an exact match).

And there can be repeats in that first column, but =vlookup() will return the
values from the first match.


Brad Vogt wrote:

If you want to stay with the vlookup function, the first column needs to be
in ascending order and there can't be any values that repeat in that column.

"Max" wrote:

You could try something like this, array-entered (press CTRL+SHIFT+ENTER to
confirm the formula):
=INDEX(C1:C4,MATCH(1,(A1:A4="a23")*(B1:B4="paper") ,0))
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pierre" wrote:
given the data below :

a23 paper 5
b14 lamp 15
a23 paper 2
a23 paper 7

i am using the vlookup and i am having a problem.
i am searching for "paper" ....but each time i am typing for a23 i get
"paper" , so how to do in order to differenciate between the words "papers"
in other words .. if i would like to combine a formula whom let me type
a23 in order to search for "paper" and who has number 5 .....how to do
it????

--

Dave Peterson


--

Dave Peterson

vlookup.
 

I'm guessing that you migrated from Lotus 123. IIRC, 123 didn't support that
4th parm.

And for the most part, if I'm matching text, I can't imagine not forcing an
exact match. (I use False as that 4th parm way more than 99% of the time <bg.)

Brad Vogt wrote:

Cool! Thanks!

I think many people will use the default in the function and not add ,false
at the end. I will remember that part for future use.

"Dave Peterson" wrote:

=vlookup("when",a1:b5,2,false)
returns "This" for me.


Brad Vogt wrote:

Put the following in cells A1:B5

How This
When Because
Does Not
What How
Where Why

If you vlookup "When" and return the 2nd column, you will get "How" as the
result, so the first column does need to be in ascending order even if it is
text. Now if you use the Match function and nest it into the vlookup, you
can make it work. Easier to just sort ascending by the first column if
possible though.

"Dave Peterson" wrote:

The first column doesn't need to be sorted for =vlookup() to work. Since it's
going to be an exact match (based on text), the fourth parm should be set to
False (look for an exact match).

And there can be repeats in that first column, but =vlookup() will return the
values from the first match.


Brad Vogt wrote:

If you want to stay with the vlookup function, the first column needs to be
in ascending order and there can't be any values that repeat in that column.

"Max" wrote:

You could try something like this, array-entered (press CTRL+SHIFT+ENTER to
confirm the formula):
=INDEX(C1:C4,MATCH(1,(A1:A4="a23")*(B1:B4="paper") ,0))
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"pierre" wrote:
given the data below :

a23 paper 5
b14 lamp 15
a23 paper 2
a23 paper 7

i am using the vlookup and i am having a problem.
i am searching for "paper" ....but each time i am typing for a23 i get
"paper" , so how to do in order to differenciate between the words "papers"
in other words .. if i would like to combine a formula whom let me type
a23 in order to search for "paper" and who has number 5 .....how to do
it????

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson