| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Vlookup - Cherry picking information from the table ar
I have managed to figure out how to use the Vlookup command to find
information in a column and to return information using the following formula: In this example, I find two like values and return some data or a message - =IF(ISNA(VLOOKUP(E2,$B$2:$E$1957,3,FALSE))=TRUE, "Product Number is not found", VLOOKUP(E2,$B$2:$E$1957,3,FALSE)) However, I need to understand how to find T1234 in list that contains T1234_TEST, I want the lookup command to match the "T1234" portion of the project name. Is there some way to key on the first 5 characters and consider this a match? In the earlier example, it was easy to find the value with the exact name - this is a bit more tricky.. Please help FACTS: Lookup Value = T1234 Table Array = T1234_Test The problem is the N/A is return and I need it to be smart enough to say, yes we've matched the first 5 characters in T1234_Test. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Vlookup - Cherry picking information from the table ar
=vlookup(e2&"*",$b$2:$e$1957,3,false)
or maybe =vlookup(left(e2,5)&"*",$b$2:$e$1957,3,false) Wannabe Efficient wrote: I have managed to figure out how to use the Vlookup command to find information in a column and to return information using the following formula: In this example, I find two like values and return some data or a message - =IF(ISNA(VLOOKUP(E2,$B$2:$E$1957,3,FALSE))=TRUE, "Product Number is not found", VLOOKUP(E2,$B$2:$E$1957,3,FALSE)) However, I need to understand how to find T1234 in list that contains T1234_TEST, I want the lookup command to match the "T1234" portion of the project name. Is there some way to key on the first 5 characters and consider this a match? In the earlier example, it was easy to find the value with the exact name - this is a bit more tricky.. Please help FACTS: Lookup Value = T1234 Table Array = T1234_Test The problem is the N/A is return and I need it to be smart enough to say, yes we've matched the first 5 characters in T1234_Test. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Vlookup - Cherry picking information from the table ar
That second formula needs to be changed to this:
=IF(ISNA(VLOOKUP("T1234*",$B$2:$E$1957,3,0)),"Prod uct Number is not found",VLOOKUP("T1234*",$B$2:$E$1957,3,0)) -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RagDyer" wrote in message ... First of all, you *don't* need the " =TRUE " in your formula. Next ... is the " T1234 " in E2... or do you intend to "hard code" it into the formula itself? If it's in cell E2, try this: =IF(ISNA(VLOOKUP(E2&"*",$B$2:$E$1957,3,0)),"Produc t Number is not found",VLOOKUP(E2&"*",$B$2:$E$1957,3,0)) If you're going to hard code it, try this: =IF(ISNA(VLOOKUP("T1234*",$B$2:$E$1957,3,0)),"Prod uct Number is not found",VLOOKUP(E2&"*",$B$2:$E$1957,3,0)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Wannabe Efficient" <Wannabe wrote in message ... I have managed to figure out how to use the Vlookup command to find information in a column and to return information using the following formula: In this example, I find two like values and return some data or a message - =IF(ISNA(VLOOKUP(E2,$B$2:$E$1957,3,FALSE))=TRUE, "Product Number is not found", VLOOKUP(E2,$B$2:$E$1957,3,FALSE)) However, I need to understand how to find T1234 in list that contains T1234_TEST, I want the lookup command to match the "T1234" portion of the project name. Is there some way to key on the first 5 characters and consider this a match? In the earlier example, it was easy to find the value with the exact name - this is a bit more tricky.. Please help FACTS: Lookup Value = T1234 Table Array = T1234_Test The problem is the N/A is return and I need it to be smart enough to say, yes we've matched the first 5 characters in T1234_Test. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Vlookup - Cherry picking information from the tabl
Change your "false" to true, or leave it blank. Then sort your look up table
by column 1. anitadai "Dave Peterson" wrote: =vlookup(e2&"*",$b$2:$e$1957,3,false) or maybe =vlookup(left(e2,5)&"*",$b$2:$e$1957,3,false) Wannabe Efficient wrote: I have managed to figure out how to use the Vlookup command to find information in a column and to return information using the following formula: In this example, I find two like values and return some data or a message - =IF(ISNA(VLOOKUP(E2,$B$2:$E$1957,3,FALSE))=TRUE, "Product Number is not found", VLOOKUP(E2,$B$2:$E$1957,3,FALSE)) However, I need to understand how to find T1234 in list that contains T1234_TEST, I want the lookup command to match the "T1234" portion of the project name. Is there some way to key on the first 5 characters and consider this a match? In the earlier example, it was easy to find the value with the exact name - this is a bit more tricky.. Please help FACTS: Lookup Value = T1234 Table Array = T1234_Test The problem is the N/A is return and I need it to be smart enough to say, yes we've matched the first 5 characters in T1234_Test. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Vlookup - Cherry picking information from the table ar
Another fuzzy match play to try ..
Place in say, F2, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(LEN(E2)=0,"",IF(ISNA(MATCH(TRUE,ISNUMBER(SEARC H(TRIM(E2),TRIM($B$2:$B$1957))),0)),"Product Number is not found",INDEX($D$2:$D$1957,MATCH(TRUE,ISNUMBER(SEAR CH(TRIM(E2),TRIM($B$2:$B$1957))),0)))) Copy F2 down -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Wannabe Efficient" wrote: I have managed to figure out how to use the Vlookup command to find information in a column and to return information using the following formula: In this example, I find two like values and return some data or a message - =IF(ISNA(VLOOKUP(E2,$B$2:$E$1957,3,FALSE))=TRUE, "Product Number is not found", VLOOKUP(E2,$B$2:$E$1957,3,FALSE)) However, I need to understand how to find T1234 in list that contains T1234_TEST, I want the lookup command to match the "T1234" portion of the project name. Is there some way to key on the first 5 characters and consider this a match? In the earlier example, it was easy to find the value with the exact name - this is a bit more tricky.. Please help FACTS: Lookup Value = T1234 Table Array = T1234_Test The problem is the N/A is return and I need it to be smart enough to say, yes we've matched the first 5 characters in T1234_Test. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Conditional Vlookup - Cherry picking information from the tabl
I think the wild card suggestion will work.
anitadai wrote: Change your "false" to true, or leave it blank. Then sort your look up table by column 1. anitadai "Dave Peterson" wrote: =vlookup(e2&"*",$b$2:$e$1957,3,false) or maybe =vlookup(left(e2,5)&"*",$b$2:$e$1957,3,false) Wannabe Efficient wrote: I have managed to figure out how to use the Vlookup command to find information in a column and to return information using the following formula: In this example, I find two like values and return some data or a message - =IF(ISNA(VLOOKUP(E2,$B$2:$E$1957,3,FALSE))=TRUE, "Product Number is not found", VLOOKUP(E2,$B$2:$E$1957,3,FALSE)) However, I need to understand how to find T1234 in list that contains T1234_TEST, I want the lookup command to match the "T1234" portion of the project name. Is there some way to key on the first 5 characters and consider this a match? In the earlier example, it was easy to find the value with the exact name - this is a bit more tricky.. Please help FACTS: Lookup Value = T1234 Table Array = T1234_Test The problem is the N/A is return and I need it to be smart enough to say, yes we've matched the first 5 characters in T1234_Test. -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup on pivot table results = #N/A | Excel Worksheet Functions | |||
| charting how many widgets over time | Charts and Charting in Excel | |||
| VLOOKUP & Conditional Formating Help. | Excel Discussion (Misc queries) | |||
| Table Array in VLOOKUP Relies on Data Validation | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Linear Mode
