Vlookup

[cDuck28Z]

Hey, i'm trying to use VLOOKUP but the field i am searching is a complex formula and it wont recognize any of the values. Is there some trick to it??

Column I is what i am working with..

[Cheeky Charlie]

VLOOKUP(G2,C:D,2,FALSE)

G2 is not found in column C

Did you mean:
VLOOKUP(G2,a:D,4,FALSE)
?

[cDuck28Z]

VLOOKUP(G2,C:D,2,FALSE)

G2 is not found in column C

Did you mean:
VLOOKUP(G2,a:D,4,FALSE)
?

No, because some of the numbers in G2 ARE found in column C. If you drag the formula that i have down, it doesn't recognize a single thing unless i manually type in the exact value (not calculate it with the formula in Column C)

[Cheeky Charlie]

No, because some of the numbers in G2 ARE found in column C

Help me to help you. I'm not here for a game of Cluedo. Which?

[martindwilson]

its because of col g are not in same format as col c amongst other things! g is custom formatted 000000 to make them look 6 characters long like the text values on col c so that's one reason

[ljungren13]

Your version:
=IF(G2="","",IF(COUNTIF(A:A,G2),VLOOKUP(G2,C:D,2,FALSE),"Obsolete"))
You haven't fully defined the logical test in the if statement, and your column D is empty? so your vlookup range will not return anything



Is this what you are hoping for?
=IF(G2="","",IF(COUNTIF(A:A,G2)>0,VLOOKUP(G2,A:B,2,FALSE),"Obsolete"))

[cDuck28Z]

Help me to help you. I'm not here for a game of Cluedo. Which?

G16 for example.

G16 is 050141RD, H16 is C.
C302 is 050141RD, D302 is C

All of these numbers match up so i column I should be blank for this particular example, but i am still getting a #N/A with my formula

its because of col g are not in same format as col c amongst other things! g is custom formatted 000000 to make them look 6 characters long like the text values on col c so that's one reason

Could this be problem? I've changed them both to Text format with no luck. I've also tried custom format 000000 and it too had no effect. I've tried copying all of column C and doing a special paste of just values and again, no change. I have to be doing something wrong because i know there is data that matches up in both columns..

[Cheeky Charlie]

Your version:
=IF(G2="","",IF(COUNTIF(A:A,G2),VLOOKUP(G2,C:D,2,FALSE),"Obsolete"))
You haven't fully defined the logical test in the if statement, and your column D is empty? so your vlookup range will not return anything



Is this what you are hoping for?
=IF(G2="","",IF(COUNTIF(A:A,G2)>0,VLOOKUP(G2,A:B,2,FALSE),"Obsolete"))

Ifs will interpret 0 as false and any value other than 0 as true, so you don't need >0

[Cheeky Charlie]

G16 for example.

G16 is 050141RD, H16 is C.
C302 is 050141RD, D302 is C

Your formula in Column C leaves a trailing space - '050141RD C' remove the C = '050141RD '

Simple fix =trim(your current formula)

But it is pretty unwieldy, I'm confident there'd be a better way of writing that formula, again, not Sherlock Holmes, if you want to explain what that formula is for we could probably simplify it for you.

HTH

PS mdw, I love you, but formatting will never affect a lookup (except for misleading people about what is being looked up)

[cDuck28Z]

Your formula in Column C leaves a trailing space - '050141RD C' remove the C = '050141RD '

Simple fix =trim(your current formula)

GREAT find!!! I didn't realize that b4 it work great now!!!
Thank you SO much! This is such a great help!!