how to use vlookup in excel to find matching data

#How to use vlookup in excel to find matching data how to#
#How to use vlookup in excel to find matching data code#
#How to use vlookup in excel to find matching data download#

The table is not set in ascending order.

The data we want to lookup does not exist in our table.

To hide the #NA error we can use the Vlookup and ISNA functions together.įirst, let us understand the reasons why the vlookup error #N/A occurs: There are many error-trapping functions like ISNA, IFNA, IFERROR, and ISERROR which we can use in Excel.

#How to use vlookup in excel to find matching data how to#

Here we discuss how to use VLOOKUP For Text in excel along with practical examples and a downloadable excel template.In this article we will learn how to use ISNA formula with Vlookup function to find matching values in different sheets in Excel. In order to find the text values, errors turn on the error checking.To convert the number looking text values to numerical values, we can use the VALUE function.To convert numerical values to text values, we can use the TRIM function as well as the TEXT function.Things to Remember About VLOOKUP For Text You should get the result for your VLOOKUP function. In order to convert the number value to text, mention the format text argument as zero (0).Īfter applying the VLOOKUP Formula answer is shown below.Īs usual select remaining arguments of the VLOOKUP function and complete the formula. Open VLOOKUP & TEXT function together in cell E3 and select the lookup value as cell D3. What if the data table itself is in text format, but your lookup values are in numerical format. We have seen how to deal with when the lookup value is in text format. Method #5 – Convert Numerical Values to Text using TEXT Function

#How to use vlookup in excel to find matching data code#

Now we should get values of pin code against each country code.

Select the lookup table, mention column index number and range lookup type. Open VLOOKUP & VALUE in the same formula as shown in the below image.

Firstly before we supply the lookup value to the VLOOKUP function, we need to convert the numerical looking text value to the actual number, so nest the function VALUE inside the VLOOKUP to convert it to a number. Using this function, we can convert number looking text values to number format in excel. If you are aware, we have a function called VALUE in excel. Method #4 – Convert Text Values to Numerical Values using VALUE Function Once you complete this process, you should get the result with the VLOOKUP function. Keep the VLOOKUP formula as it is already applied.Įnter number 1 in cell H1 and Copy the number.Īfter copying the cell H1, select the range D3 to D22.Īfter selecting the range D3 to D22, press ALT + E + S + M. We can convert all the number looking text values to numerical values using Paste Special method. There are several ways you get rid of this.Įxample #3 – Convert Text Values to Numerical Values using Paste Special Method Nothing to worry I will show you the way to get rid of this such situation. I think you must have encountered this kind of situation when the actual data is coming from different servers. You can say we can convert these numbers from text values to number values, yes we can, but still, we do not get the results. This seems to be unrealistic at this point in time, isn’t it? One of the common problems in these cases is numbers are stored as text values. We have got an error value of #N/A against all the city codes. Let’s apply the VLOOKUP function to extract the Pin Code for each city. The first thing that comes to our mind is the VLOOKUP function yes, you are correct we can fetch the data by applying the VLOOKUP function. In table 2, we have only City Code and based on this we need to extract the Pin code from table 2. In table 1, we have city code and pin code against city code. Example #2 – VLOOKUP Function to Extract the Data This is the scenario in excel when the numbers stored as text values. Let’s apply the SUM formula in cell B5.Īfter applying the formula, we will get the result.Īfter applying the formula, the result is shown below. In Column B, Numerical Values are stored as Numbers, and in Column C, Numbers are stored as text values so we can get the total of the numbers.

#How to use vlookup in excel to find matching data download#

You can download this VLOOKUP For Text Excel Template here – VLOOKUP For Text Excel Template Example #1 – Numbers Stored as Text Value