# Thread: Vlookup problem pertining to two sheets

1. ## Vlookup problem pertaining to two sheets

On sheet “Applicable Branch” Col H, I am trying to look up the branch code for the applicable stock number. The branch codes are on sheet "Stock Sheet" .

Where "CB","AVI TRADERS","CN" appears on sheet "Stock Sheet", the formula must then look up the branch code on sheet "Branch Names not on Stock Sheet"

The formula is not yielding the correct result for eg XXMRJB9CB should be returning CB2, XXMRJ1EG should be returning CN1 etc

It would be appreciated if someone could kindly assist me

2. Chuck,

Here's a formula that's a bit more readable (avoids the confusing, IMHO, SumProduct):
=IF(bCheckBranch(VLOOKUP(B14,'Stock sheet'!\$I\$1:\$T\$43,12,FALSE)),VLOOKUP(B14,'Branch Names not on Stock Sheet'!\$A\$1:\$B\$8,2,FALSE),VLOOKUP(B14,'Stock sheet'!\$I\$1:\$T\$43,12,FALSE))

You'll notice it uses a helper UDF bCheckBranch:
Code:
```Option Explicit

Function bCheckBranch(zBr As String) As Boolean

bCheckBranch = IIf(InStr("CN*CB*AVI TRADERS", UCase(zBr)), -1, 0)

End Function 'bCheckBranch```
Some observations:
Try to use shorter sheet names when using code.
Avoid spaces in sheet names, if readability is your goal use underlines vs spaces.
Use Range names for your lookup (table) ranges makes code shorter and easier to adjust.

I hope I understood what you were trying to accomplish.

HTH

3. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

HowardC (2016-07-26)

4. Hi RG

Kindly amend your function to accept both upper and lower case

Howard

5. Howard,

The function as it stands will accept either.

You'll notice the UCase() function in there which takes the passed value and convert it to upper case for the purpose of the comparison.

HTH

6. My Apologies. I had a type, so I thought that txt had to be in upper case

7. Hi Howard

aside from RG's method, you could use a 'helper column' to check the first lookup, then check the returned value against an entry in a named range.

see attached file.

I used a named range [block1] on a new sheet named [lookups], and a helper column [I].
No coding required, and you can easily add more entries to the named range [block1] if required.

zeddy

8. ## The Following User Says Thank You to zeddy For This Useful Post:

HowardC (2016-07-26)

9. Thanks Zeddy

This is also very useful

Howard

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•