| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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???? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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???? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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???? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Hybrid Mode
