Copying/pasting lookup values for a vlookup formula gives N/A, but typing values gives correct results

0
votes

I'm trying to get a VLOOKUP to pull results based on lookup values I copy and paste from another sheet.

The formula works when I type the values in and hit enter, but when I copy a list of values into the column from another sheet the VLOOKUP returns an N/A value. Both the column of values and the column used for the VLOOKUP formula are formatted as 'Text' and pasting as a value hasn't worked.

Does anyone know why this is happening? Or how to get around this?

Formula for reference: =IF(VLOOKUP(A3,Data_Exported!$A:$CB,2,FALSE)=0,"",VLOOKUP(A3,Data_Exported!$A:$CB,2,FALSE))

1
excelvlookup
VLOOKUP can return N/A for many reasons. Are you sure that a match can be found in the table? Without seeing your data, it is very difficult for someone here to help you. Please edit your question to include screenshots of the data in your spreadsheet. – ImaginaryHuman072889
maybe you have spaces or other unprintable characters in some of the data that is throwing off the match. – Scott Craner
Are you coping cells that have formulas? I usually always try to use PasteSpecial values. That way it gets rid of any formatting, formulas, or anything else other than the actual text value. – R. Roe
ImaginaryHuman072889 - The matches are found when I manually type the values and hit enter. I'm not sure a screenshot would help because it would be all blank in column A and a bunch of #N/A in all the other columns until I manually entered a value. – InvaderCyn
Scott Craner - I changed the formatting on the values I tried copying/pasting to 'Text' wouldn't that show spaces or unprintable characters? – InvaderCyn

1 Answers

0
votes

I had this same problem and I figured out that I did not use absolute cell reference, when you highlight the box after you click your initial cell you want to click F4 on your keyboard to make it an absolute cell reference. for example, =VLOOKUP(C8,B12:C22,2,FALSE) right after you put in B12:C22 click F4 on your keyboard to initiate absolute cell reference. Your formula will then look like this, =VLOOKUP(C8,$B$12:$C$22,2,FALSE) I know this was asked a long time ago but I hope this helps everyone with my problem. Thank you.