Should I try this in VBA

[LeeJames]

I have in column B the following formula

IF(F2>8997543,(LEFT(H2,(FIND("/",H2)-1))),(LEFT(H2,(FIND("-",H2)-1))))

This returns a unique reference which I then want to run a vlookup off in column C

=VLOOKUP(B2,lookup!J:K,2,FALSE)

I was going to nest the lookup but even separate I'm getting #NA

I have the lookup table formatted as number.

What am I doing wrong

 

[steve the fish]

What is in H2?

 

[LeeJames]

H2 contains a transaction ref "6747626810/JUN17"

 

[steve the fish]

Left will produce a textual number rather than a real number and vlookup will not see these as the same even if they are visually. Are your numbers in the lookup table textual or real numbers? You can find out by =ISNUMBER(A1) for example.

 

[LeeJames]

The lookup table contains real numbers. I ran ISNUMBER to be on the safe side

 

[steve the fish]

Ok then using your formula:

=IF(F2>8997543,(0+LEFT(H2,(FIND("/",H2)-1))),(0+LEFT(H2,(FIND("-",H2)-1))))

 

[LeeJames]

That's great! it works, thank you