| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLookup 1st 9 char of Lookup range
Hi,
I'm having trouble figuring out how to do this. I need to look up the value in E346 (9 char long) and compare it to just the left 9 characters in my look up range and return the value in column G. E is a string. G is a number. Here's a couple I've tried: =IF($E346<"",VLOOKUP($E346,LEFT($E36:$G336,9),3,F ALSE),"") =IF($E346<"",INDEX(G36:G336,MATCH($E346,LEFT($E36 :$E336,9),0),"")) Can this be done? -- Thanks for your help. Karen53 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup 1st 9 char of Lookup range
Try it like this:
=IF($E346<"",VLOOKUP($E346&"*",$E36:$G336,3,0),"" ) The "*" is a wildcard. -- Biff Microsoft Excel MVP "Karen53" wrote in message ... Hi, I'm having trouble figuring out how to do this. I need to look up the value in E346 (9 char long) and compare it to just the left 9 characters in my look up range and return the value in column G. E is a string. G is a number. Here's a couple I've tried: =IF($E346<"",VLOOKUP($E346,LEFT($E36:$G336,9),3,F ALSE),"") =IF($E346<"",INDEX(G36:G336,MATCH($E346,LEFT($E36 :$E336,9),0),"")) Can this be done? -- Thanks for your help. Karen53 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup 1st 9 char of Lookup range
Hi,
Thank you for your reply. Cell E346 is complete. I'm actually trying to E346 to the left 9 char of E36:G336. I took your suggestion and modified it but it is not working. =IF($E346<"",VLOOKUP($E346,$E36&"*":$G336&"*",3,F ALSE),"") I tried it with Match as well =IF($E346<"",INDEX(G36:G336,MATCH($E346,$E36&"*": $E336&"*",9),0),"")) -- Thanks for your help. Karen53 "T. Valko" wrote: Try it like this: =IF($E346<"",VLOOKUP($E346&"*",$E36:$G336,3,0),"" ) The "*" is a wildcard. -- Biff Microsoft Excel MVP "Karen53" wrote in message ... Hi, I'm having trouble figuring out how to do this. I need to look up the value in E346 (9 char long) and compare it to just the left 9 characters in my look up range and return the value in column G. E is a string. G is a number. Here's a couple I've tried: =IF($E346<"",VLOOKUP($E346,LEFT($E36:$G336,9),3,F ALSE),"") =IF($E346<"",INDEX(G36:G336,MATCH($E346,LEFT($E36 :$E336,9),0),"")) Can this be done? -- Thanks for your help. Karen53 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup 1st 9 char of Lookup range
I took your suggestion and modified it but it is not working.
Try it *just* like I posted it! =IF($E346<"",VLOOKUP($E346&"*",$E36:$G336,3,0),"" ) -- Biff Microsoft Excel MVP "Karen53" wrote in message ... Hi, Thank you for your reply. Cell E346 is complete. I'm actually trying to E346 to the left 9 char of E36:G336. I took your suggestion and modified it but it is not working. =IF($E346<"",VLOOKUP($E346,$E36&"*":$G336&"*",3,F ALSE),"") I tried it with Match as well =IF($E346<"",INDEX(G36:G336,MATCH($E346,$E36&"*": $E336&"*",9),0),"")) -- Thanks for your help. Karen53 "T. Valko" wrote: Try it like this: =IF($E346<"",VLOOKUP($E346&"*",$E36:$G336,3,0),"" ) The "*" is a wildcard. -- Biff Microsoft Excel MVP "Karen53" wrote in message ... Hi, I'm having trouble figuring out how to do this. I need to look up the value in E346 (9 char long) and compare it to just the left 9 characters in my look up range and return the value in column G. E is a string. G is a number. Here's a couple I've tried: =IF($E346<"",VLOOKUP($E346,LEFT($E36:$G336,9),3,F ALSE),"") =IF($E346<"",INDEX(G36:G336,MATCH($E346,LEFT($E36 :$E336,9),0),"")) Can this be done? -- Thanks for your help. Karen53 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup 1st 9 char of Lookup range
Hello,
Try it as an array formula? {=index($G$36:$G$336,match(1,($E346=left($E$36:$G$ 336,9)),0))} HTH, JP On Oct 19, 2:36 pm, Karen53 wrote: Hi, Thank you for your reply. Cell E346 is complete. I'm actually trying to E346 to the left 9 char of E36:G336. I took your suggestion and modified it but it is not working. =IF($E346<"",VLOOKUP($E346,$E36&"*":$G336&"*",3,F ALSE),"") I tried it with Match as well =IF($E346<"",INDEX(G36:G336,MATCH($E346,$E36&"*": $E336&"*",9),0),"")) -- Thanks for your help. Karen53 "T. Valko" wrote: Try it like this: =IF($E346<"",VLOOKUP($E346&"*",$E36:$G336,3,0),"" ) The "*" is a wildcard. -- Biff Microsoft Excel MVP "Karen53" wrote in message ... Hi, I'm having trouble figuring out how to do this. I need to look up the value in E346 (9 char long) and compare it to just the left 9 characters in my look up range and return the value in column G. E is a string. G is a number. Here's a couple I've tried: =IF($E346<"",VLOOKUP($E346,LEFT($E36:$G336,9),3,F ALSE),"") =IF($E346<"",INDEX(G36:G336,MATCH($E346,LEFT($E36 :$E336,9),0),"")) Can this be done? -- Thanks for your help. Karen53- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup 1st 9 char of Lookup range
Hi,
Thank you! Yes, it worked but it doesn't seem to make sense. The wild card applies to the range even though it is attached to E346? Would you give me the logic behind it? I'd like to understand what is happening. Thanks again! -- Thanks for your help. Karen53 "T. Valko" wrote: I took your suggestion and modified it but it is not working. Try it *just* like I posted it! =IF($E346<"",VLOOKUP($E346&"*",$E36:$G336,3,0),"" ) -- Biff Microsoft Excel MVP "Karen53" wrote in message ... Hi, Thank you for your reply. Cell E346 is complete. I'm actually trying to E346 to the left 9 char of E36:G336. I took your suggestion and modified it but it is not working. =IF($E346<"",VLOOKUP($E346,$E36&"*":$G336&"*",3,F ALSE),"") I tried it with Match as well =IF($E346<"",INDEX(G36:G336,MATCH($E346,$E36&"*": $E336&"*",9),0),"")) -- Thanks for your help. Karen53 "T. Valko" wrote: Try it like this: =IF($E346<"",VLOOKUP($E346&"*",$E36:$G336,3,0),"" ) The "*" is a wildcard. -- Biff Microsoft Excel MVP "Karen53" wrote in message ... Hi, I'm having trouble figuring out how to do this. I need to look up the value in E346 (9 char long) and compare it to just the left 9 characters in my look up range and return the value in column G. E is a string. G is a number. Here's a couple I've tried: =IF($E346<"",VLOOKUP($E346,LEFT($E36:$G336,9),3,F ALSE),"") =IF($E346<"",INDEX(G36:G336,MATCH($E346,LEFT($E36 :$E336,9),0),"")) Can this be done? -- Thanks for your help. Karen53 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup 1st 9 char of Lookup range
{=index($G$36:$G$336,match(1,($E346=left($E$36:$G$ 336,9)),0))}
Won't work like that. Replace the 1 with TRUE. -- Biff Microsoft Excel MVP "JP" wrote in message oups.com... Hello, Try it as an array formula? {=index($G$36:$G$336,match(1,($E346=left($E$36:$G$ 336,9)),0))} HTH, JP On Oct 19, 2:36 pm, Karen53 wrote: Hi, Thank you for your reply. Cell E346 is complete. I'm actually trying to E346 to the left 9 char of E36:G336. I took your suggestion and modified it but it is not working. =IF($E346<"",VLOOKUP($E346,$E36&"*":$G336&"*",3,F ALSE),"") I tried it with Match as well =IF($E346<"",INDEX(G36:G336,MATCH($E346,$E36&"*": $E336&"*",9),0),"")) -- Thanks for your help. Karen53 "T. Valko" wrote: Try it like this: =IF($E346<"",VLOOKUP($E346&"*",$E36:$G336,3,0),"" ) The "*" is a wildcard. -- Biff Microsoft Excel MVP "Karen53" wrote in message ... Hi, I'm having trouble figuring out how to do this. I need to look up the value in E346 (9 char long) and compare it to just the left 9 characters in my look up range and return the value in column G. E is a string. G is a number. Here's a couple I've tried: =IF($E346<"",VLOOKUP($E346,LEFT($E36:$G336,9),3,F ALSE),"") =IF($E346<"",INDEX(G36:G336,MATCH($E346,LEFT($E36 :$E336,9),0),"")) Can this be done? -- Thanks for your help. Karen53- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup 1st 9 char of Lookup range
Nevermind, my idea tanked.
On Oct 19, 2:51 pm, JP wrote: Hello, Try it as an array formula? {=index($G$36:$G$336,match(1,($E346=left($E$36:$G$ 336,9)),0))} HTH, JP |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup 1st 9 char of Lookup range
Hi T. Valko,
Never mind. I get it. The wild card would have to be on E346 to make up for the missing chars. Thanks again! -- Thanks for your help. Karen53 "T. Valko" wrote: I took your suggestion and modified it but it is not working. Try it *just* like I posted it! =IF($E346<"",VLOOKUP($E346&"*",$E36:$G336,3,0),"" ) -- Biff Microsoft Excel MVP "Karen53" wrote in message ... Hi, Thank you for your reply. Cell E346 is complete. I'm actually trying to E346 to the left 9 char of E36:G336. I took your suggestion and modified it but it is not working. =IF($E346<"",VLOOKUP($E346,$E36&"*":$G336&"*",3,F ALSE),"") I tried it with Match as well =IF($E346<"",INDEX(G36:G336,MATCH($E346,$E36&"*": $E336&"*",9),0),"")) -- Thanks for your help. Karen53 "T. Valko" wrote: Try it like this: =IF($E346<"",VLOOKUP($E346&"*",$E36:$G336,3,0),"" ) The "*" is a wildcard. -- Biff Microsoft Excel MVP "Karen53" wrote in message ... Hi, I'm having trouble figuring out how to do this. I need to look up the value in E346 (9 char long) and compare it to just the left 9 characters in my look up range and return the value in column G. E is a string. G is a number. Here's a couple I've tried: =IF($E346<"",VLOOKUP($E346,LEFT($E36:$G336,9),3,F ALSE),"") =IF($E346<"",INDEX(G36:G336,MATCH($E346,LEFT($E36 :$E336,9),0),"")) Can this be done? -- Thanks for your help. Karen53 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup 1st 9 char of Lookup range
Exactly!
You're welcome! -- Biff Microsoft Excel MVP "Karen53" wrote in message ... Hi T. Valko, Never mind. I get it. The wild card would have to be on E346 to make up for the missing chars. Thanks again! -- Thanks for your help. Karen53 "T. Valko" wrote: I took your suggestion and modified it but it is not working. Try it *just* like I posted it! =IF($E346<"",VLOOKUP($E346&"*",$E36:$G336,3,0),"" ) -- Biff Microsoft Excel MVP "Karen53" wrote in message ... Hi, Thank you for your reply. Cell E346 is complete. I'm actually trying to E346 to the left 9 char of E36:G336. I took your suggestion and modified it but it is not working. =IF($E346<"",VLOOKUP($E346,$E36&"*":$G336&"*",3,F ALSE),"") I tried it with Match as well =IF($E346<"",INDEX(G36:G336,MATCH($E346,$E36&"*": $E336&"*",9),0),"")) -- Thanks for your help. Karen53 "T. Valko" wrote: Try it like this: =IF($E346<"",VLOOKUP($E346&"*",$E36:$G336,3,0),"" ) The "*" is a wildcard. -- Biff Microsoft Excel MVP "Karen53" wrote in message ... Hi, I'm having trouble figuring out how to do this. I need to look up the value in E346 (9 char long) and compare it to just the left 9 characters in my look up range and return the value in column G. E is a string. G is a number. Here's a couple I've tried: =IF($E346<"",VLOOKUP($E346,LEFT($E36:$G336,9),3,F ALSE),"") =IF($E346<"",INDEX(G36:G336,MATCH($E346,LEFT($E36 :$E336,9),0),"")) Can this be done? -- Thanks for your help. Karen53 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup 1st 9 char of Lookup range
Thanks Biff! Kept getting an error and couldn't figure out why.
--JP On Oct 19, 2:59 pm, "T. Valko" wrote: {=index($G$36:$G$336,match(1,($E346=left($E$36:$G$ 336,9)),0))} Won't work like that. Replace the 1 with TRUE. -- Biff Microsoft Excel MVP "JP" wrote in message oups.com... Hello, Try it as an array formula? {=index($G$36:$G$336,match(1,($E346=left($E$36:$G$ 336,9)),0))} HTH, JP |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
VLookup 1st 9 char of Lookup range
Your original formula would work if you coerce the logical expression to 1
or 0: =index(G36:G336,match(1,--(E346=left(E36:E336,9)),0)) But, matching TRUE takes one less processing cycle since you don't need to coerce the logical expression. =index(G36:G336,match(TRUE,E346=left(E36:E336,9),0 )) You would use a match of 1 when there are multiple conditions: =index(G36:G336,match(1,(E346=left(E36:E336,9))*(F 3:F336<""),0)) Multiplying the logicals together will coerce the result to a 1 or 0. -- Biff Microsoft Excel MVP "JP" wrote in message oups.com... Thanks Biff! Kept getting an error and couldn't figure out why. --JP On Oct 19, 2:59 pm, "T. Valko" wrote: {=index($G$36:$G$336,match(1,($E346=left($E$36:$G$ 336,9)),0))} Won't work like that. Replace the 1 with TRUE. -- Biff Microsoft Excel MVP "JP" wrote in message oups.com... Hello, Try it as an array formula? {=index($G$36:$G$336,match(1,($E346=left($E$36:$G$ 336,9)),0))} HTH, JP |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup using dates in the lookup range | Excel Worksheet Functions | |||
| VLOOKUP using a tab name to reference the lookup range | Excel Discussion (Misc queries) | |||
| Vlookup problem in lookup range | Excel Worksheet Functions | |||
| VLOOKUP range lookup | Excel Worksheet Functions | |||
| Range Lookup in VLookup ?'s | Excel Worksheet Functions | |||
Linear Mode
