VLookup 1st 9 char of Lookup range

[Guest]

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,FALSE),"")

=IF($E346<>"",INDEX(G36:G336,MATCH($E346,LEFT($E36:$E336,9),0),""))

Can this be done?

 

[T. Valko]

Try it like this:

=IF($E346<>"",VLOOKUP($E346&"*",$E36:$G336,3,0),"")

The "*" is a wildcard.

 

[Guest]

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,FALSE),"")

I tried it with Match as well
=IF($E346<>"",INDEX(G36:G336,MATCH($E346,$E36&"*":$E336&"*",9),0),""))

--
Thanks for your help.
Karen53

Try it like this:

=IF($E346<>"",VLOOKUP($E346&"*",$E36:$G336,3,0),"")

The "*" is a wildcard.

 

[T. Valko]

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

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,FALSE),"")

I tried it with Match as well
=IF($E346<>"",INDEX(G36:G336,MATCH($E346,$E36&"*":$E336&"*",9),0),""))

 

[JP]

Hello,

Try it as an array formula?

{=index($G$36:$G$336,match(1,($E346=left($E$36:$G$336,9)),0))}


HTH,
JP

 

[Guest]

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!

 

[T. Valko]

{=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.

 

[JP]

Nevermind, my idea tanked.

 

[Guest]

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!

 

[T. Valko]

Exactly!

You're welcome!

 

[JP]

Thanks Biff! Kept getting an error and couldn't figure out why.

--JP

 

[T. Valko]

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))*(F3:F336<>""),0))

Multiplying the logicals together will coerce the result to a 1 or 0.