MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by DPM on May 07, 2001 1:24 PM

My vlookup formula doesn't work if the lookup_value is a cell reference whose value is derived from a formula as opposed to a "typed in" value. The vlookup just returns a #N/A. Any way around this? Here's my vlookup formula: =VLOOKUP(B2,Sheet2!$A:$B,2,FALSE). B2 is a reference to a formula cell - the formula in that cell is: =RIGHT(A2,4). Boy, this stuff gets confusing after a while!

Posted by Barrie Davidson on May 07, 2001 1:41 PM

Your vlookup should work with that formula (I've used formula references a lot). Are you sure that you have an exact match for =RIGHT(A2,4) in your lookup range (Sheet2!$A:$B)?

Posted by IML on May 07, 2001 1:57 PM

Just a guess

Is your look range numbers? If so it will not find a match based on the text value of the =right formula. If this is the case, modify your formula to =RIGHT(A2,4)*1 to turn it into a number. If you could return letters, you may have to use a isnumber formula
good luck.

Posted by DPM on May 07, 2001 2:04 PM

Re: Just a guess

BINGO! - adding the *1 did the trick!!! Thanks very much for the help!!!