Table of Contents

Recently, some users reported that they encountered a vlookup cell format error.

Approved

  • 1. Download ASR Pro
  • 2. Open the program and select "Scan your computer"
  • 3. Click "Repair" to start the repair process
  • The software to fix your PC is just a click away - download it now.

    Another common source of #N/A errors associated with VLOOKUP formulas are numbers formatted as text or in the main secondary lookup table. This usually happens when importing data from a physical database, or when entering this apostrophe before a number to illustrate leading zeros.

    Today’s researcher, Greg Truby, Excel MVP, discusses some of the common problems customers may encounter when using the VLOOKUP function.

    This article assumes a normal level of familiarity with the VLOOKUP() function whenand people have the easiest way to look up a key value in one spreadsheet or block of data, but still return a block of points associated with a second spreadsheet. or dataframe. When using VLOOKUP(), you often run into three common problems:

  • We need to find something based on multiple columns

  • We find #N/A ourselves, although the key is undoubtedly valid

  • We ran into a problem with Zoolander

  • Problem #1. We need to look up the column a bit, but we each need to look up a pair of keys, not just one. Note that we need to return status information from the following table.

    Produce

    color

    Status

    Apples

    Red

    Washington

    Cherry

    Red

    Approved

    The ASR Pro repair tool is the solution for a Windows PC that's running slowly, has registry issues, or is infected with malware. This powerful and easy-to-use tool can quickly diagnose and fix your PC, increasing performance, optimizing memory, and improving security in the process. Don't suffer from a sluggish computer any longer - try ASR Pro today!


    Michigan

    Bananas

    Yellow

    Hawaii

    Lemons

    vlookup cell format error

    Yellow

    Texas

    Grapes

    Green

    California

    Apples

    Green

    Pennsylvania

    Grapes

    Purple

    New York

    We can’t just use the “color” column: would “red” mean Washington? Or Michigan? We can’t just produce a column – probably my Washington “apples”? Or Pennsylvania? We really want to test both Produce and Color.

    Solution Problem #1. The simplest solution is to create a helper column that uses both buttons at the same time. To improve readability, you can add any type of separator between two fields, such as a vertical bar (|), a comma, or perhaps a semicolon. When we concatenate two, three, or more pieces of text (often referred to as “text lines” or even just “strings”), we are doing a concatenation process that results in “concatenation”. The foundation of concatenation in Excel is the ampersand (&), because our helper formula (using the pipe character) would look like this:

    After placing the helper column, formula and return, we usually get the following table. Note that our merged column should still be there and the left column is similar to their data we are going to return.

    Produce

    color

    Compound

    Status

    Apples

    Red

    Apples|Red

    Cherry

    Washington

    Red

    Cherry|Red

    vlookup cell format error

    Michigan

    Bananas

    Yellow

    Bananas|Yellow

    Hawaii

    Lemons

    Yellow

    Lemon|Yellow

    Texas

    Grapes

    Green

    Grapes|Green

    California

    Apples

    Green

    Apples|Green

    Pennsylvania

    Grapes

    Purple

    Grapes|Purple

    New York

    You can create a similar large multi-column that You can use the VLOOKUP() function in it, or you can try concatenation inside the VLOOKUP() formula. For a great example, check out the formula below in the bartender formula.

    Problem 2: We find data matches, but VLOOKUP() returns #N/A.

    Solution #2: The problem is probably still that the keys are a large combination of numeric values ​​and words in the cells, and only one of the formatted key columns is considered GENERAL and the other is separated as TEXT. Here, the key of the individual table is formatted in general, and the key of the right table is formatted in text.

    key

    Flowers

    Enter second

    VP()

    Р

    Roses

    9

    #N/A

    4

    Eyelets

    4

    #N/A

    Lily

    l

    Р

    Roses

    9

    Zinnias

    T

    Tulips

    To fix this lovely bug, you need to validate and properly format numeric values ​​such as “Number”. The lookup value is not in the first smile of the table board. Typically, the value you are looking for must be in one of the first (leftmost) columns of the particular table_array argument of the VLOOKUP function.

    The first solution that usually comes to mind is “I’ll just type the ‘General’ column as text” (or vice versa). So we select the column type and press Ctrl+F1 (or Home | Format | Format Cells (2007, 2010) or Format | Cells… (2003 and below)) and change all formats and…. Which!? It doesn’t solve the problem. Changing the format of the actual cell doesn’t happen until you see the edit cell. If we’ve zoomed in more than a few lines, our team won’t want to go back and press F2 and then ENTER a few hundred times. We have two more options. One is to use Excel’s built-in true error if it told us about the element. In the screenshot below, it is. Then we need to select all the cells in that column and choose “Convert to Number” from the error handling context menu.

    If we’re not sure about the ability to fix errors, or just prefer the method, we can use the Text to Columns tool instead. To do this, select the column we all want to re-format, then from the menu (using Excel 2005 here) choose Data | Text into columns… and we’ll seeim a master that looks like this:

    We can just move the DELIMITED option in place and hit Next > and then make sure the delimiter we need to check isn’t actually next to your column. Usually just sticking to TAB is sufficient because the product is very rare in some cells. Then we click “Next>” again and in step 3 we use the “General” format we need, click “Text” and finish.

    As long as we remember the required format, some of us should be rewarded that VLOOKUP() works correctly with this function.

    An alternative solution: just in case we get adventurous, we can perform a type conversion in the formula, essentially forcing the data types. If the structures that come first in the VLookup argument are formatted as TEXT, and therefore the keys that are in the new range that is in the next argument are formatted as GENERAL, then something like:

    The software to fix your PC is just a click away - download it now.

    Solution. If your company is confident that the relevant data is in your spreadsheet and the VLOOKUP function definitely doesn’t capture it, take the time to make sure there are no hidden spaces or non-printable cartoon characters in the specified cells. Also make sure the cells are of the correct data type.

    General personal formatting cannot be applied by formula, so the VLOOKUP function cannot do it for you. He really would like to be VBA.

    the best solution The most common cause of the #N/A error is usually XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, or MATCH, i.e. when the formula cannot find the value being called. For example, your search result is not in the original search. In this case, there is little or no “banana” in the poker lookup table, so the functionVLOOKUP returns #N/A error.

    To use a format other than vlookup, the first step is to look for a normal value, as usual. To fix #N/A, use the IFERROR function to capture #N/A cases.

    Surprisingly, VLookup N/A is only formatted as text format for cells, but that also depends on the value and type of your new data. When your data is collected, make sure the number you see is the same, especially numbers.