If that’s not possible, then try moving your columns. Solution: You can try to fix this by adjusting your VLOOKUP to reference the correct column. In this case, Excel is looking for it in column A, not column B.
The #N/A error results because the lookup value “Kale” appears in the second column (Produce) of the table_array argument A2:C10. In the following table, we want to retrieve the number of units sold for Kale. If your lookup value is not in the first column of the array, you will see the #N/A error. One constraint of VLOOKUP is that it can only look for values on the left-most column in the table array. Problem: The lookup value is not in the first column in the table_array argument
#How to use vlookup in excel 2000 vba pdf#
You can share the PDF with others or print for your own reference. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.Tip: Also, refer to the Quick Reference Card: VLOOKUP troubleshooting tips which presents the common reasons for #NA issues in a convenient PDF file. Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. This is slightly more advanced and covered in the Advanced Excel course. This is where you mimic the FIND by telling the VLOOKUP to find cells that CONTAIN the lookup as opposed to being exactly the same as the lookup. To change the formats from numbers to text you can use the VALUE and TEXT functions.Īll of these options are addressed in the Intermediate Excel course.Īnother option is to do a partial match with the VLOOKUP. You can use TRIM to get rid of any unnecessary spaces. Best to do it on both the lookup value and table array.
#How to use vlookup in excel 2000 vba how to#
How to fix the VLOOKUP problemįirst and easiest is to create a new lookup column. If you did the same thing in A6, you would find no spaces and that’s the reason for the #N/A.Įither way, if you can see it and VLOOKUP can’t it will in all likelihood be a space or format issue. You can use your arrow keys to see how many spaces there are after the text. So below note in cell A10, the cursor was placed far away from the text (to the right) and when we clicked, the blinking icon wasn’t right next to the text but several characters away. Rather click as far to the right as possible. When doing this make sure you don’t click right next to the text. If you do a FIND and you find the item, but VLOOKUP didn’t, click into the two offending cells and check for spaces.
First test- VLOOKUP can’t find it, but FIND can So having a space at the end of a cell is difficult for a human to spot but is never ignored by Excel. Although humans don’t see spaces as characters, a space is a character in Excel. The most common reason this happens though is due to leading or trailing spaces. So if you were looking for ABC, then it would ignore 123ABC456. VLOOKUP looks for a cell that matches in the ENTIRE cell. So if you were looking for ABC, it would find it in a cell that contained 123ABC456. First to understand the difference between how VLOOKUP finds items and how the FIND tool finds them.įIND looks for any cell that CONTAINS the lookup value.
0 kommentar(er)
