Vlookup reporting #N/A's
What's the deal....I have legitimate data in both the lookup column as well
as the return column, both formatted as numbers, as well as my formula cell, but upon looking up it is finding nothing. Can someone give me some common errors to check for as far as using the VLOOKUP function? TIA, Greg |
It's not uncommon for a cell's value to *look* like a number but be text, so
Excel will not match it when comparing it to a real, true number. This is espcially common when one of the numbers, or your lookup table, for instance, comes from an external source. You can try =VLOOKUP(VALUE(lookup value),table,...) - which will fix it if your lookup value is actually text. If that doesn't work, copy a blank cell, select the first column of your lookup table, and EditPaste SpecialAdd. That will force the lookup table keys to be numeric. Good luck "D&E Communications" wrote: What's the deal....I have legitimate data in both the lookup column as well as the return column, both formatted as numbers, as well as my formula cell, but upon looking up it is finding nothing. Can someone give me some common errors to check for as far as using the VLOOKUP function? TIA, Greg |
Further to Dukes advice, check to see if there are any leading or trailing
spaces in the table, especially if the data has been imported. The TRIM function will remove them or simply Edit Replace 'Space' with nothing. If the data came from a web site, they have habit of using the ASCII character 160 which looks like a space, (ASCII32), but isn't. Use =CODE(your cell) to check, if that returns 160 then you have them. To remove these if they exist, highlight the whole table, use Edit Replace. Replace What, hold down the Alt key and type 0160 on the numeric keypad, (nothing will appear in the box), Replace With, Nothing, Replace All, Regards, Alan. "Duke Carey" wrote in message ... It's not uncommon for a cell's value to *look* like a number but be text, so Excel will not match it when comparing it to a real, true number. This is espcially common when one of the numbers, or your lookup table, for instance, comes from an external source. You can try =VLOOKUP(VALUE(lookup value),table,...) - which will fix it if your lookup value is actually text. If that doesn't work, copy a blank cell, select the first column of your lookup table, and EditPaste SpecialAdd. That will force the lookup table keys to be numeric. Good luck "D&E Communications" wrote: What's the deal....I have legitimate data in both the lookup column as well as the return column, both formatted as numbers, as well as my formula cell, but upon looking up it is finding nothing. Can someone give me some common errors to check for as far as using the VLOOKUP function? TIA, Greg |
All times are GMT +1. The time now is 03:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com