vlookup.

[pierre]

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????

 

[Max]

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

 

[Brad Vogt]

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.

 

[Dave Peterson]

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]

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]

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

 

[Brad Vogt]

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]

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>.)

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.