If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
TROUBLESHOOTING VLOOKUP - PASTE NEW VALUES
I have a workbook where column "I" looks up the value
in "J" in another worksheet and returns a correct value for column "I" which works for 400+ rows just fine. When I delete the values in "J", then paste new ones I receive via e-mail attachment, I get #N/A instead of the lookup values. I have played with formating as text, general, number, etc. But I cannot get this to work properly 100%. It works for 10% - 80% of the pasted values. Sometimes keying over the pasted value will cause vlookup to work. Thanks in advance for any help here. |
#2
|
|||
|
|||
TROUBLESHOOTING VLOOKUP - PASTE NEW VALUES
Hi Charlie,
The numbers may look the same but the lookup value may be a number stored as text. Find an offending lookup value and use this formula to check if it really matches the other sheet lookup value: =A1=I5. Where A1 is the lookup value in the formula and I5 is in the table array. If it says FALSE you will need to clean up your pasted in lookup values. If the paste in info is always going to give you trouble perhaps adding this to your formula will help. Note that the A1 lookup value is multiplied by 1 to return it to a number. If it is already a number, no harm no foul. =VLOOKUP(A1*1,D1:E5,2,0) HTH Regards, Howard "Charlie Marcous" wrote in message ... I have a workbook where column "I" looks up the value in "J" in another worksheet and returns a correct value for column "I" which works for 400+ rows just fine. When I delete the values in "J", then paste new ones I receive via e-mail attachment, I get #N/A instead of the lookup values. I have played with formating as text, general, number, etc. But I cannot get this to work properly 100%. It works for 10% - 80% of the pasted values. Sometimes keying over the pasted value will cause vlookup to work. Thanks in advance for any help here. |
Thread Tools | |
Display Modes | |
|
|