Vlookup Is Not Working? – Use VBA MyVLOOKUPData User’s Function

Do you need stable VLOOKUP search results? But sometimes VLOOKUP Excel function returns #NA error? I tried to fix, change cell formatting using “Format Cells” to General, Text, Number, Custom, but still not worked… Thanks to the God, we can use Visual Basic for Application (VBA), which supports by MS Excel many-many years. All we need is to compare data in cells converting them to String variables using VBA. MyVLOOKUPData User’s Function does that task and you will get reliable results. Works with numbers, large vendor codes, etc. in any cell formatting. Because of internal VBA conversion MyVLOOKUPData function works up to 10 times lower, than VLOOKUP, because VLOOKUP is the internal Excel function. But to get reliable results in most cases you will be satisfied when using MyVLOOKUPData function.

Keywords: vlookup not working, value not available error vlookup, vlookup not work with numbers, vlookup #n/a, vlookup value error, vlookup fixing error, vlookup not finding a value, how to fix your excel vlookup errors

1. Download ZIP Archive with MyVLOOKUPData.bas VBA module from Gumroad service. It’s free, but any donation (price more than $1 is appreciated). Extract MyVLOOKUPData.zip to your Hard drive. MD5 Checksum: A8B2B2B38A23C9A79F04E58F8B355A42
2. Open your Excel Worksheet file, save to another copy with XLSM extensions (Excel Macro-Enabled Workbook).
3. Press “ALT+F11”, then import “MyVLOOKUPData.bas” from File - > Import File (CTRL+M), as shown

4. Close “Microsoft Visual Basic for Applications” Window.
5. Save your new Excel Macro-Enabled Workbook again.
6. Now, to get right search results with “MyVLOOKUPData” function, please modify your XLSM file as shown below.
Main requirements are:
Your Listing must be started from 1st row always. There are no any other limitations.

7. For easy understanding we use another sheet with “Search” name. For our example we are trying to find Tittles with some prices. For B2 cell we use formula: “=MyVLOOKUPData(A2;Listing!$B$1:$B$28;Listing!$A$1:$A$28)” as shown. Your XLSM Workbook knows MyVLOOKUPData User’s function and you can apply it as many times as you need. Accordingly to standard VLOOKUP function, you need to adjust formula value, using mouse or keyboard navigation. A2 cell contains the value, which we search on “Listing” sheet in Price B column. As the exact first value will be found, Title from A column on “Listing” sheet will be returned as function result to cell on “Search” sheet. When you have entered formula for B2 cell, you can copy this formula to other cells. To fix search range, in MyVLOOKUPData we use “Listing!$B$1:$B$28” and “Listing!$A$1:$A$28” fixed Excel ranges. Please check “MyVLOOKUPData_Sample_File.xlsm” with our example from MyVLOOKUPData.zip. One more time – “MyVLOOKUPData” will return first founded value. There are 3 products with “1299900” price, in rows 18, 19 and 20. MyVLOOKUPData returned Title from 18th row.

8. Have more questions? Send them to gtuignatov@yandex.ru
9. Please share link to this page in your social networks.