*The tutorial explains how you can quickly cope with VLOOKUP not working problems in Excel 2016, 2013, 2010, 2007 and 2003, troubleshoot and fix common errors and overcome VLOOKUP's limitations.*

In the last few articles, we have explored different aspects of the Excel VLOOKUP function. If you have been following us closely, by now you should be an expert in this area : )

However, it's not without a reason that many Excel specialists consider VLOOKUP to be one of the most intricate Excel functions. It has a ton of limitations and specificities, which are the source of various problems and errors.

In this article, you will find simple explanations of VLOOKUP's #N/A, #NAME and #VALUE error messages as well as solutions and fixes. We will start with the most frequent cases and most obvious reasons why vlookup is not working, so it might be a good idea to check out the below troubleshooting steps in order.

## Fixing VLOOKUP N/A error in Excel

In Vlookup formulas, the #N/A error message (meaning "not available") is displayed when Excel cannot find a lookup value. There can be several reasons why that may happen.

#### 1. A typo or misprint in the lookup value

It's always a good idea to check the most obvious thing first : ) Misprints frequently occur when you are working with really large data sets consisting of thousands of rows, or when a lookup value is typed directly in the formula.

#### 2. #N/A in approximate match VLOOKUP

If you are using a formula with approximate match (range_lookup argument set to TRUE or omitted), your Vlookup formula might return the #N/A error in two cases:

- If the lookup value is smaller than the smallest value in the lookup array.
- If the lookup column is not sorted in ascending order.

#### 3. #N/A in exact match VLOOKUP

If you are searching with exact match (range_lookup argument set to FALSE) and the exact value is not found, the #N/A error is also returned. See more details on how to properly use exact and approximate match VLOOKUP formulas.

#### 4. The lookup column is not the leftmost column of the table array

As you probably know, one of the most significant limitations of Excel VLOOKUP is that it cannot look to its left, consequently your lookup column should always be the **left-most column in the table array**. In practice, we often forget about this and end up with VLOOKUP not working because of the N/A error.

**Solution**: If it is not possible to restructure your data so that the lookup column is the left-most column, you can use a combination of Excel's INDEX and MATCH functions, as a more versatile alternative to VLOOKUP. You will find the detailed info and a formula example in this tutorial - INDEX / MATCH formula to lookup values to left.

#### 5. Numbers are formatted as text

Another source N/A errors in VLOOKUP formulas is numbers being formatted as text, either in the main or lookup table.

This usually occurs when you import data from some external database or if you've typed an apostrophe before a number to indicate a leading zero.

The most obvious indications of numbers being formatted as text are shown in the screenshot below.

The numbers can also be stored in the General format. In this case, there's only one noticeable sign - numbers get aligned to the left side of a cell, while numbers stored as numbers are aligned to the right by default.

**Solution:** If this is just a single number, simply click on the error icon and choose "*Convert To Number*" from the context menu.

If multiple numbers are affected, select them all, right-click the selection, then choose *Format Cells* > *Number tab* > *Number* and click OK.

#### 6. Excess leading or trailing spaces

This is the least obvious cause of the Vlookup N/A error because a human eye can hardly spot those extra spaces, especially when working with big tables where most of the entries are below the scroll.

**Solution 1: Extra spaces are in the main table (with VLOOKUP formulas)**

If excess spaces occur in your main table, you can ensure the correct work of your Vlookup formulas by wrapping the lookup_value argument with the TRIM function:

`=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)`

**Solution 2: Extra spaces are in the lookup table (lookup column)**

If extra spaces occur in the lookup column, there is no easy way to avoid VLOOKUP #N/A errors. Instead of VLOOKUP, you can use an array formula with a combination of INDEX / MATCH and TRIM functions:

`=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))`

Since this is an array formula, don't forget to press Ctrl + Shift + Enter rather than a usual Enter keystroke to properly complete it:

For more information about using INDEX / MATCH in Excel, please check out this tutorial: INDEX & MATCH in Excel - a better alternative to VLOOKUP.

**Tip.**A quick alternative to complex INDEX / MATCH formulas is running the Trim Spaces for Excel add-in that will eliminate excess spaces both in the lookup and main tables in seconds, making your VLOOKUP formulas error-free. This is a free tool and you can find the download link on the above page.

## #VALUE error in VLOOKUP formulas

In general, Microsoft Excel displays the #VALUE! error if a value used in the formula is of a wrong data type. In respect to VLOOKUP, there are two common sources of the VALUE! error.

#### 1. Lookup value exceeds 255 characters

Please be aware that the VLOOKUP function cannot look up values containing 256 characters or more. If your lookup values exceed this limit, you will end up having the VALUE error:

**Solution**: Use an analogous INDEX /MATCH formula instead. In the above example, the following INDEX / MATCH function works perfectly:

`=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7= F$2,0),0))`

You can learn more about using INDEX / MATCH in Excel in this tutorial.

#### 2. Full path to the lookup workbook is not supplied

If you are pulling data from another workbook, you have to include the full path to that file. More precisely, you have to enclose the workbook's name (including the extension) in square brackets [], and then specify the sheet's name followed by the exclamation mark. Also, you should have apostrophes around all this in case either a workbook or spreadsheet name contains spaces.

Here's the structure of the complete formula to do a vlookup from another workbook:

A real formula might look similar to this:

`=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)`

The above formula will search for the value of cell A2 in column B of Sheet1 in the "*New Prices*" workbook, and return a matching value from column D.

If any element of the path is missing, your VLOOKUP formula won't work and return the #VALUE error (unless the lookup workbook is currently open).

For more info about VLOOKUP formulas referencing another Excel file, please check out this tutorial: How to do vlookup from a different workbook.

#### 3. The col_index_num argument is less than 1

It's hard to imagine a situation when someone would want to enter a number less than "1" to specify the column to return values from. Though, it may happen if this argument is returned by some other Excel function nested in your Vlookup formula.

So, if the col_index_num argument happens to be less than 1, your Vlookup formula will return the #VALUE! error too.

If the col_index_num argument is greater than the number of the columns in the specified table array, Vlookup formulas return the #REF! error.

## VLOOKUP #NAME error

This is the easiest case - the NAME error appears if you've accidentally misspelled the function's name.

The solution is obvious - check the spelling : )

## Excel VLOOKUP not working (limitations, issues and solutions)

Apart from having a fairly complicated syntax, VLOOKUP has arguably more limitations than any other Excel function. Because of these limitations, seemingly correct Vlookup formulas might often deliver results different from what you expect. Below you will find solutions for a few common scenarios when VLOOKUP fails.

#### 1. VLOOKUP is case-insensitive

The VLOOKUP function does not distinguish case and teats lowercase and UPPERCASE characters as identical. So, if your table includes several similar entries that differ in the uppercase or lowercase chars only, the Vlookup formula will return the first found value regardless of the case.

**Solution**: Use another Excel function that can do a vertical lookup (LOOKUP, SUMPRODUCT, INDEX / MATCH) in combination with the EXACT function that can match case. You can find the detailed explanations and formula examples in this tutorial - 4 ways to do a case-sensitive vlookup in Excel.

#### 2. VLOOKUP returns the first found value

As you already know, Excel VLOOKUP returns the first value it finds in the return column that matches the lookup value. However, you can force it to bring the 2^{nd}, 3^{rd}, 4^{th} or any other occurrence you want. If you need to get all duplicate occurrences, you will have to use a combination of the INDEX, SMALL and ROW functions.

**Solutions**: Formula examples are available for download here:

#### 3. A new column was inserted or removed from the table

Regrettably, VLOOKUP formulas stop working every time when a new column is deleted from or added to a lookup table. This happens because the syntax of the VLOOKUP function requires that you supply the entire table array as well as a certain number indicating which column you want to return the data from. Naturally, both the table array and the return column's number change when you remove an existing column or insert a new one.

**Solution**: INDEX / MATCH comes to the rescue again : ) In INDEX & MATCH formulas, you specify the lookup and return columns separately, and as a result you can delete or insert as many columns as you want without worrying about updating every associated vlookup formula.

#### 4. Cell references changed when copying the formula to other cells

The heading gives an exhaustive explanation of the problem, right?

**Solution**: Always use absolute cell references (with the $ sign) in table_array, e.g. $A$2:$C$100 or $A:$C. In the formula bar, you can quickly switch between different reference types by pressing F4.

## VLOOKUP with IFERROR / ISERROR

If you do not want to intimidate your users with all those N/A, VALUE or NAME error messages, you can return a blank cell instead, or display your own message. You can do this by wrapping your VLOOKUP formula in the IFERROR function in Excel 2016, 2013, 2010 and 2007 or with IF / ISERROR in earlier Excel versions.

#### Using VLOOKUP with IFERROR

The syntax of the IFERROR function is simple and self-explanatory : )

Meaning, you enter the value to check for an error in the 1^{st} argument, and in the 2^{nd} argument you specify what to return if an error occurs.

For example, the following IFERROR / VLOOKUP formula returns a blank cell when the lookup value is not found:

`=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")`

If you'd rather display your own message instead of a standard Vlookup error, type it between the quotation marks, like this:

`=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Oops, no match is found. Please try again!")`

#### Using VLOOKUP with ISERROR

Since the IFERROR function was introduced in Excel 2007 only, in lower Excel versions you will have to use the combination of IF and ISERROR functions in this way:

*VLOOKUP formula*), "

*Your message if any*",

*VLOOKUP formula*)

For example, here's the IF / ISERROR / VLOOKUP formula analogous to the IFERROR / VLOOKUP formula above:

`=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)), "", VLOOKUP($F$2,$B$2:$C$10,2,FALSE))`

That's all for today. Hopefully, this short tutorial will help you cope with all possible VLOOKUP errors and get your formulas to work in the way you want.

We had a problem when we upgraded to 2013.

When we sorted a table with a column containing the VLOOKUP formula

=VLOOKUP(Analysis!D3,DEPT2,2,FALSE)

The formula kept the reference to original cell that we were referring to

So, if the above formula was located on row 3, and the sort moved it to row 41, it would still reference D3, rather than D41

The table was located in the worksheet named "Analysis", so that worksheet reference was superfluous, and when we removed Analysis! from the formula, it adapted correctly to the new row following the sorting of the table.

Hello Derek,

Thank you very much for sharing this information. I've tested this behavior and got the same results.

you're so pretty

Thank you Svetlana

you are really sweet girl. what you are presenting here of useful material reflects your sweetness, kind heart and sole.

Thank you again love you and keep on.

Cheers

Jafar

Saudi Arabia

Thank you very much for your nice words, Jafar! You are too kind.

Dear,

thank you for the great information , but I review all the possible problem and I fix them , but steel #N/A! and the Looked up value is exist!!

so here is the lookup: =VLOOKUP(A2,C2:D1048534,1,FALSE)

the column A have numbers which I need to make sure if it's exist or not then give me the value for it's reference. and C, D are the array index.

and I used False because it's phone number I need the exact match.finally 1 will return the subscription date.

they both have the same type , I check with =TYPE() , also they are match I checked with A2=D3.

:) so how can I fix it?

thx

Thank you so much for your Manual uploaded

Please help!

Disclaimer: I'm a newbiew with excel. I've created a table which has a couple hundred items which is referenced to by a vlookup. For some reason, when I am using the drop down to select an item, the drop down only shows 196 items from the table. Nothing more. The table array in the formula is correct and locked. BUt for some reason the dropdown list will only go so far. Help?

Hi Chris,

It's hard to determine the cause of the problem without seeing your data. Anyway, this is not because of Excel limitations. Even in older versions of Excel, a limit to the number of items displayed in drop-down lists was 1,000. In modern versions it's 10,000.

It's just not working. I used the wizard to build the formula =VLOOKUP('Tracking In Progess'!A:A,A:A,1,'Tracking In Progess'!A:C) and it always returns #VALUE!-- I tried swapping the lookup_value and table_array and it always returns #VALUE! I've tried doing this with five different workbooks with different data. It never works. I've tried it in 2010 and 2013 and the result is always the same. I need this function -- but I hate it!

It works now that I followed Derek's comment.

I have been struggling for hours , you saved my life. I would have never guessed that the lookup column had to be the leftmost column of the table array..

This is very helpful! Thank You. Unfortunately, my VLOOKUP is still not working and I'm receiving the #N/A. I know what the issue is, but can't fix it. I am looking in a range that doesn't contain the VLOOKUP value I would like (which is ok) and I just want it to return to zero (0). Is there any way I can do that in excel?

found the answer:

add Iferror to your formula:

=IFERROR(VLOOKUP($R2;'my database'!$E:$U;6;FALSE);" ")

Hi Sveltana,

My problem was a silly one my lookup search column wasn't sorted so it was returning a few N/A# but now it's solved. I owe you one and you got my gratitude it was really appreciated.

Thank you very much for posting this is really useful.

i what to compare my list of names, Numbers, dates in 2 sperate excel spreadsheet for each of them to finds erros.for example, i want boths spread sheet to have save figure 1245 and 1245. it if any contain 1254 then how can i corret it. example 2, i want stanley on both spreadsheet, it the ohter contain stanely, i can excel tell me the teh mistake and how can i fix it.

can anyone help me please.

1- Can i use vlookup formula to search for data in an excel sheet that has blank rows or columns?

2- can i build my vlookup formula on the right of the data that I need to search it?

thanks,

Tony

Hi Tony,

1) yes, the VLOOKUP formula can be used to search through blank rows/columns.

2) yes, it's up to You where You place your formula.

I have an Excel 2010 document that has vlookups and other formulas. I want to share the Excel doc so that my team can update their parts and we can all be in the file. The only problem I am having is that when the document is being shared and I go to update it with new data (I am the only one on the document when I add new data) for everyone to update the vlookups stop working. What can be causing this issue?

THANK YOU!

The problem I frequenly run in to is the data that is being displayed by the Vlook up is both correct and incorrect. It pulls data from the wrong cell. Im pritty sure I set everything up correctly but why would it present incorrect data why not just N/A or error? In addition some of the data is represented properly. Seems sporadic on one sheet driving me nuts.

Hello,

Most likely the issue is in your data or in the way they are presented: the cells format, excess spaces, merge cells, etc.

I have a spreadsheet that we update daily on one tab and we use a vlookup to update information on the main tab. The problem is that there are several client names that won't update automatically after we paste the new data. We have to clear their name each day and retype it for the vlookup to work. I have ran trim and that doesn't work. Plus my colleague cannot even update the name. The vlookup won't work at all if she updates from her excel. The names are in alphabetical order. The range is covered. THe names aren't all at the end they really have nothing in common that I can see. It seems completely random. If I retype their names the vlookup does not return the #NA but if my colleague retypes their names her Excel does not update the data. I have gone crazy trying to figure this out. Help!

We detfniiely need more smart people like you around.

Brilliant, Thanks a lot, I've searched high and low for an answer and this is the only one that I can understand and make work.

I have been using a the same Lookup in a large spreadsheet for about 4 years now. Recently I have been getting random results. In some of the cells I get the expected results but in others I just get a 0, not N/A or #Value#. I've made sure that my table is sorted in the correct order and that the data I'm looking for is in the furthest left column. I've also made sure that I have the correct range in my formula, nothing seems to help.

What could be causing this random error in my results?

I am using excel 2013. when I try to do vlookup,the first row prints correct value. when I drag the formula for other columns it prints the same first value for all the other columns even if the formula is changed.

Hi there, I would appreciate your help.

I created a table of VLookup formulas. Worked great, but now I am trying to sum values in several rows and I get a Zero.

I would also like to average, counta and several other functions but it doesn't seem to work on the values created by VLookup.

What am I doing wrong.

Thank you so much for your help.

VLOOLUP not giveing correct number.

Never mind. I found the problem. There were duplicate values and it grabbed the largerst value

How can I search the numbers with E+ in my databases of thousands records

How can I search the numbers with E+ in my databases of thousands records.

Every time I enter a vlookup lately (I didn't use to have this problem), if returns something like =VLOOKUP(A:A,'[Restricted Detailed Employee Listing_4.14.2015_limited.xlsx]Detailed Employee Listing'!$A:$S,false,19). Please help.

hi,

I have an assignment, using vlookup and if functions,

we cannot use the "if error" to not shown the "n/a",

what function I can use instead?

Thank you

Cris

hi,

when using the following formula =VLOOKUP(A:B,Sheet2!A:C,3,2) getting value error. pls help.

I am trying to do Vlook up and it keeps on giving me #n/a

It is reading from 2 worksheets within one document;

vlookup($A3,'Venue Options'!$A:L,2,FALSE)

I have checked that my columns are in ascending order and that the cell format is set at number....

I am at a loss! I know it is difficult to see what I am doing but this is a relatively easy formula. ..

Thank you

Ali

I have a list of members per month for three months and have to combine all the members of those months and mark in front of the members who were present in that perticular month. I know that it can be done by vlookup but i am not able to do it.

Please help.

I was wondering if you could help me with an issue I have been having. I pull daily spreadsheets with information for employees. I have a column at the end that has my notes for each individual case. With each spreadsheet I use vlookup to transfer the most current notes from the previous day’s spreadsheet to the one I just pulled. The lookup value I use is the employee’s ID number which I always remember to convert to number. Every time I transfer the notes, the majority are correct but quite a few either populate as ‘0’, ‘#N/A’, or pull previous notes from much earlier spreadsheets. I’ve been on numerous excel forums looking for a solution and have tried many(locking the reference table array, sorting and filtering each spreadsheet the same way, etc) and none seem to work. I really appreciate any insight you can offer, correcting each spreadsheet has been a timely process that I feel I can be better spending doing other activities.

Hello,

Excellent post! Out of all the pages I looked at to resolve this problem, yours worked. So simple and Boom!!

may anyone help me..? during using VLOOKUP i am facing problem, actually i have a data which have extra spaces and "" quotes etc in table array but showing simple as lookup value and obviously i am unable to get the data so pls help me..

SL No Part # Description SL No Part # Description

1 1100316 PLATE SPRING UPPE 1 1100316 #N/A

2 1100611 KEY WOODRUFF CLUTCH 2 1100342 #N/A

3 1100342 BEARING BALL 3 1100611 #N/A

4 1101008 ROLLER 4 1100818 #N/A

5 1101205 SPRING RETURN 5 1101008 #N/A

6 1180123 BALL STEEL 6 1101205 #N/A

7 1100818 WASHER BRASS 7 1180123 #N/A

__________________________________________________________________

I have checked all the columns if the

I am using a vlookup to match sales and on hand data for item numbers that contain an'*' in them. The lookup is working except for occasion where the exact value is not found, but something close is found. I am told that the wildcard '*' is the reason the formula brings back a bad result even when the vlookup uses the FALSE condition (=vlookup(A1,G1:H13000,2,FALSE. The target looks like B110015BS***GG but pulls from B110015BSR**GG. Does anyone know how to make the formula ignore the wildcard? I tried doing a find/replace, but as a wildcard, it replaced the whole string in each field..

Hello, this post helps to reduce time on vlookup formula, thanks

If nothing else works for number search (i.e. excessive blanks removed, data formats made the same number format) this works for me:

=vlookup(A1/1,D1:D100,2,FALSE)

Bank Code Legend:

bpi BPI

BEGINNING BALANCE 258,581.64

ADD:

CASH DEPOSIT 270699

A/R - COLLECTIONS 530,400.00

FUND TRANSFER IN 0.00

TOTALS 801,099.00

DEDUCT:

CASH REMITTANCE 0.00

FUND TRANSFER OUT 0.00

EXPENSES 414,350.00

TOTALS 414,350.00

ENDING BALANCE TO DATE 645,330.64

Bank Code Legend:

bpi BPI

BEGINNING BALANCE 258,581.64

ADD:

CASH DEPOSIT 270699

A/R - COLLECTIONS 530,400.00

FUND TRANSFER IN 0.00

TOTALS 801,099.00

DEDUCT:

CASH REMITTANCE 0.00

FUND TRANSFER OUT 0.00

EXPENSES 414,350.00

TOTALS 414,350.00

ENDING BALANCE TO DATE 645,330.64

good day!

i just wanna know how can i show/flash the names of clients (which is in the other sheet) who paid cash in my report? same with the client who paid their accounts receivable, which i termed "collection"?

i just want to show my boss the names of clients who paid their obligations (cash payment & collection of A/R).

i used vlookup to find the details about the "bank" (bpi) the beg balance, the cash deposit, collections etc. what i want to solve now is how can i make the names of the clients appear in my report to see in details who the clients are already based on the data i encoded. and if my boss tries to see the details of another bank, same thing i want to appear the names of clients i encoded, also..

please help me..

Thanks!!

your utmost consideration and accommodation regarding my request would be of great help...please...

thanks

I keep having my vlookup column doing this. Is there something I have to do to correct this?

=VLOOKUP(A4,October!A:A,1,0)

=VLOOKUP(A4,October!A:A,1,0)

=VLOOKUP(A4,October!A:A,1,0)

=VLOOKUP(A4,October!A:A,1,0)

=VLOOKUP(A4,October!A:A,1,0)

=VLOOKUP(A4,October!A:A,1,0)

=VLOOKUP(A4,October!A:A,1,0)

=VLOOKUP(A4,October!A:A,1,0)

Hello, Dee,

You can add a check for the found value:

=IF(ISERROR(VLOOKUP(A4,October!A:A,1,0)), "",VLOOKUP(A4,October!A:A,1,0))

WHATS with wrong (( ? )))

Hi, svetlana cheusheva

i have a query related to excel please help me

how to highlight past dates?

Hope you help me!

I think Formula is =a2<today() it is work.

Thanks for your explanations. The problem with my vlookup was the formatting of the "search" data; formatting it as "number" solved the problem!

Thanks Svetlana

Svetlana

It seems you may be able to help me. I am using Excel 2010. I created a drop-down list, and want to use VLOOKUP to complete other columns, with information in the table which I used for the drop-down list. The column for the drop-down is A.

If I make my selection from the drop-down list, nothing happens, that is my formula, e.g:=VLOOKUP($E2,Premises!$A$2:$P$101,4,FALSE) remains in the cell, with no value provided. I checked formatting of cells to make sure it is the same.

Thanks

Jacoba

Hello, Jacoba,

To understand your task better, we'd like to have a look at your data. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

In certain parts of my worksheet , when i enter the vlookup function it doesnt do anything it remians as text format

Hello, Mahir,

Please make sure you enter the equal sign before adding the formula.

Hi Mahir,

Most likely it's because the format of those cells is set to "Text". Changing the cell format to "General" should fix it. If it doesn't, check out the following solutions:

Why is Excel showing a formula, not result?

Had a similar issue today with a vlookup. I tried the suggestions above but nothing worked. I had to insert a value() into the field lookup value in order to conduct the search. Oddly, the field being looked up is formatted properly as well as the range it is looking at. An example:

=VLOOKUP(VALUE(A1691),DSI!$B$2:$D$769,3,FALSE)

Hello, Brian,

To help you better, we need a sample table with your data in Excel. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

Hi,

My V-lookup is working, but the formula will not copying down the full column of data. For example, 100 cells of data, first cell will v-look up correctly, but the rest of the column can't be completed without repeating the v-lookup an additonal 99 times, in each cell. Does that make sense?

Any thoughts on how to fix?

Thank you.

Hello, Susan,

To help you better, we need a sample table with your data in Excel. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

THANK YOU!!!! You just saved me. TRIM...who knew!

I too facing the common error like #N/A, even after i convert the look up range in number format. kindly suggest. any other alter.

A problem with…

"Use VLOOKUP and INDIRECT to dynamically pull data from different sheets"

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)reference formula

Since my reference cell Y3 needs to be a number and my Named Ranges can start with a number (LIST15, LIST18, LIST20) I cannot get this formula to work.

=VLOOKUP($AE3,INDIRECT($Y3&"_LIST"),2,0)

I even tried to move the underscore to the right side to match the way it is in the Named Ranges and still don’t work.

=VLOOKUP($AE3,INDIRECT($Y3&"LIST_"),2,0)

I get a #REF! Error in both cases. Can anyone help?

Hello, I've been having some trouble using this formula.

I've got this code that I have to divide in three parts which I've resolved using LEFT,MID and RIGHT functions. Each part has to be reidentified with some data, using VLOOKUP I have identified the first and last part (which were letters), and I tried using this function for the middle part (which are numbers) but it returns #N/A. I tried changing the cell format from text to numbers but it's the same result

Code: q-15-PAL

=MID(B2,3,2) = 15

=VLOOKUP(E2,M2:N38,2) = Palanca (this is what's supposed to appear)

can somebody help me?

Thanks !!

Hi experts,

is it possible to look for a value which is the formula? I tried vlookup but not working, when I put look up value manually then it`s fine.

thanks for help!

tom

Hello,

I am facing problem during using of VLOOKUP formula , Row & column number not showing.. anyone can give your advice?

nice tricks

Hi,

I have two excel sheets one contains insurance policies issued by the insurance company through insurance broker along with the premium amount and the commission amount and another excel sheets which is provided by the insurance broker which has policies issued on behalf of the insurance company it has premium amount and the commission amount. Now my question is, i want to match both excel sheets by using vlookup. I matched large volume of data. But, when the same policy number is appears twice or thrice, vlookup showing the policy which comes first and ignores the remaining two. I want to know about the policy which are appearing twice or thrice with relevant premium amount. I read the how to get all duplicate values in the vlookup range. However, it seems like it is small volume of data. How do i it for the large volume of data?

Thank you so much, Svetlana, for the troubleshooting tips! Without them, I was struggling for hours trying to get VLOOKUP to work across two excel workbooks. What finally worked for me were sorting the lookup column in ascending order, and bringing that column to lie AFTER the starting column index of the Lookup table (as you suggested above). Many thanks, especially as I did not find such trouble shooting steps in Excel also.

Hi,

I have two worksheets, one with the list of names with related information in each row, and the other is for the query. I provide the name in one of the cells of my query sheet, and I want to use the MATCH function to get the row number (from the names worksheet) of the specified name.

I am trying to use the following:

=MATCH($B$3,$B$2!A1:A10, 0)

Thanks in advance.

-Ravi

Where I specify the required name to be queried in the B3 cell of the query sheet, and B2 cell has the name of the names sheet.

In short, I want to use variables to specify the lookup-value and lookup-range. How do I go?

very gud I enjoy

Hello:

We have an unusual problem with VLOOKUP. First, the format we use:

=VLOOKUP(A6,dec2016register,7,FALSE)

This command is in the 2017 workbook and is retrieving a number from another separate 2016 workbook.

Where dec2016register is the named array for a worksheet in one workbook and has a value we want to export to a worksheet within another, separate workbook. Both workbooks are open, both reside in the same folder. We defined the array, there is no data conflict with the Lookup Value in either worksheet, and the exact same command (with different arrays) works perfectly in one other application involving two separate workbooks. BUT with this one particular application, we keep getting a #NAME? error. Why? What are we doing wrong???

Hi,

I am trying to create a vlookup and it is not getting executed. The below formula is shown in the column instead of the result

=VLOOKUP(A3,'[11i Extract 300000.xlsx]Sheet1'!$A$1:$A$3995,1,false)

This is driving me nuts. The VLOOKUP worked on my 2015 data. but when I replicated it with 2016, using the same formulas, the lookup fails retunring #N/A...

Col B is a list of names, such as

Name

Jaclyn Smith - Sr

Yvette Burcescu - Jr

Mychael Vernon - Fr

Kiara Adams - Jr

the lookup array is

Name_(11) Name Section Award

Aida Rossi Aida Rossi - Valhalla All Conference Honorable Mention

Alexandra A Alexandra Antony - Blind Brook All Conference Honorable Mention

Amanda Marj Amanda Marji - Yonkers All Conference Honorable Mention

Anjali Seba Anjali Sebastian - Clarkstown North All Section

Formula is =IFERROR(VLOOKUP(LEFT(B42,11),Names_Awards_2016,3,FALSE),"") returns an #N/A error, however, if I replace the LEFT(B42,11) with the actual texts string ex:"Jaclyn Smit", the formula appears to work.

Help.

while using vlookup for row and column detail not showing... pls help

Hi

how can I use some Vlookups in a cell;means I want to Vlookup some tables and show one of answer(or even first on)in a cell?

The symbol &(between Vlookups) didn't work here!:-(

thanks for Help

AMS

I have a tale on excel file that contain vlookup function when i open that excel file on other network computer its convert to range what is the problam with that anyone please help me

Hi

How can i change format of 112-65-4171

to

812-48-7524

I have been struggling with the following issue and all the recommended fixes have not worked.

I am using vlookup to pull data from four other sheets. I need to be able to use the results in cell to trigger the next search, however what happens is that the next vlookup looks up the values in the formuala, not the result.

I.E.

=VLOOKUP(E3, DATA!A2:Z480, 4, FALSE)

and in E3 I have:

=VLOOKUP(A2, OTHER! A1:Z400, 2, FALSE)

Instead of looking for the displayed value in E3, it goes chasing down the previous VLOOKUP and tries to find A2, and if THAT was another formula, it just keeps going.

I have changed all cell formats to general, number or anything but text and have turned off 'display formulas'.

Anyone got any suggestions?

Thank you.

I need some VLOOKUP formula help

I have pay grade coded as 4-X1, 4-X2 representing salary 50,000 and 65,000 in two columns.

I get #N/A error using vlookup function when looking for any pay grade.. for example 4-X1

Why do I get this error? please assist!!

Very nice post. Solve my problem.

Hi All,

There is a problem when I try to compare two excel sheets using vlookup and if both the columns are empty which are compared. It returns an error. what to do in this condition???

Best Regards,

Faraz

AMAZING WORK THANK YOU VERY MUCH

I have 2 sheets with below data and i want to copy data(value) from sheet 2 to sheet1

sheet1:

id value

1

3

2

sheet2:

1 A

2 BB

3 C

vlookup used: =VLOOKUP(Sheet2!A2,Sheet2!A2:B4,2,0)

Result:

1 A

3 BB

2 C

Expected:

1 A

3 C

2 BB

I am looking for exact match for the value being searched. However, it is giving me values as per sequence.

Any help would be appreciated.

Convert to number, convert to number, convert to number..... I always forget to do that :P

Hello!

I am doing match. If we put =iferror...;" ") we get blank cells instead of #N/A. That is ok, BUT in that case I want to keep my original number. Is it possible?

Thank you for the answer!

Hi,

In vlook up is it possible that value can be increased in row?

Exp

=Vlookup(A3,a2:C8,2,false)

And in second row

=Vlookup(A3,a2:c8,3,false)

,2,&,3, value increased with dragging

i have a spreedsheet that i am running the following function thru =REPLACE($D344,1,2,VLOOKUP(LEFT($D344,2)+0,$AB$3:$AF$14,2,0)). Column D contains 4 digit values, ranging from 3700-37DF, i do have a few cells in coulmn D that contain 3A00, 3B00, 3C00 and on these cells i am getting #VALUE! errors. can someone help?

Good solutions..i'm really happy on seeing your solutions n solve many issues easily:) :)

Hey All,

I have been looking into a few options to try and rectify a lookup I am doing which pulls from another tab. Currently I can replace the blank cells which are showing as 0 with the following formula, however I now also need to eradicate n/as that show when the lookup returns no match against the cell targeted. Please see below:

=IF(VLOOKUP([extract]],Table13[[Num]:[extract1 ]],13,FALSE)="","",VLOOKUP([extract],Table13[[Num]:[extract1]],13,FALSE))

The above works fine, however returns n/a's. I have tried to use "iferror" without success. I need to exclude both 0's and n/a's

Thanks.

In Excel 2013 you can go do the following to remove the zeros:

File>Options>Advanced>Display options for this worksheet: (use the drop down arrow to select the correct worksheet if more than one in your file).

Make sure that the following option is deselected: Show a zero in cells that have a zero value

Good luck with the #N/A issue.

You can also try the following for the "N/A issue:

File>Options>Formulas>Error checking rules:

Make sure that the following option is deselected: Formulas referring to emtpy cells

Hi, Will you be kind enough to explain following formula

=IFERROR(INDEX($B$4:$F$15,MATCH(1,(INDEX($B$4:$F$15,,MATCH(L$10,$B$3:$F$3,0))"")*($A$4:$A$15=$K11),0),MATCH(L$10,$B$3:$F$3,0)),"")

I would appreciate if part by part formula is explained

Thanks in advance!!

Hi,

hope you can help. I am using the vlookup function from an exported spreadsheet. the cells in the column are a combination of of text or dashes and some appear as #NAME? When I click into the cell, the actual words appear but not when you look at the column. excel is reading it as a formula but it is not.

How do I fix it? I have searched for answers but not able to find a solution.

thanks

THANK YOU! I learned a valuable lesson and was able to solve my problem!: "Always use absolute cell references (with the $ sign) in table arrays, e.g. $A$2:$C$100 or $A:$C."

You're a lifesaver, thanks!

Thank you I couldn't get my VLookup to work and your article worked.when they work its brilliant when they don't your tearing your hair out. Great article.

I've had a few #NA errors in the past but they can be corrected in the following fashion

1) if a vlookup returns NA and you need to populate a cell with a number use as follows:

"=IF(ISNA(VLOOKUP(D1,A1:B3,2,0)=TRUE),0,VLOOKUP(D1,A1:B3,2,0))

a 1000 cell d1 = d

b 2000

c 3000

2)

if a vlookup returns NA and you need to populate a cell with a piece of text use as follows:

"=IF(ISNA(VLOOKUP(D6,A6:B8,2,0)=TRUE)," ",VLOOKUP(D6,A6:B8,2,0))

1 a cell d6=4

2 b

3 c

The following formula returns a blank cell

I have the following formula to pull column 33 from the URL spreadsheet specified. I get the #NA, Did not find value in vlookup evaluation.

=VLOOKUP(E1,IMPORTRANGE("1EG2Qcodvx1IkbUGbJRuBKHoOYtMLRCY5a73_8uWI","SEPT!$A$1:$AG"), 33, 0)

I also tried this:

=transpose (query(importrange("1EG2Qcodvx1IkbUGbvQeJRuBKHoOYtMLRCY5a73_8uWI","SEPT!$A$1:$AG"),"Select Col33 where Col4 = """&E1&""" ",FALSE)) and it pulls in the correct column info, but it gives me the cell above the one referenced. In other words, I am looking up the email address to return the total number of available days and if it is email address on row 9, it gives me the email address for row 8.

Do you have any suggestions?

thank you

Vlook Up is skipping identical data with no change in formula. Vlook Up entered at column AO. Referencing column AL and pulling from column 2 of array (which contains Program name) located on different tab of same file. The NA77BIO reference is unchanged, yet #N/A is returned further down in the sheet.

=VLOOKUP(AL20022,'2017-18 National Accounts (2)'!8:163,2,FALSE)

NA45STL 002 ACTIVE NATIONAL STERLING INC./SIGNET

NA77BIO 002 ACTIVE NATIONAL BIOMAT

NA77BIO 002 ACTIVE NATIONAL BIOMAT

NA77BIO 002 ACTIVE NATIONAL BIOMAT

NA77BIO 002 ACTIVE NATIONAL BIOMAT

NA77BIO 002 ACTIVE NATIONAL BIOMAT

NA77BIO 002 ACTIVE NATIONAL BIOMAT

NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK

NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK

NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK

NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK

NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK

NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK

NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK

NA91AGI 002 ACTIVE NATIONAL ALLEGIS GROUP / TEKSYSTEMS / AEROTEK

NA00SRS 002 ACTIVE NATIONAL SEARS / KMART

NA00SRS 002 ACTIVE NATIONAL SEARS / KMART

NA00SRS 002 ACTIVE NATIONAL SEARS / KMART

NA00SRS 002 ACTIVE NATIONAL SEARS / KMART

NA00SRS 002 ACTIVE NATIONAL SEARS / KMART

NA00SRS 002 ACTIVE NATIONAL SEARS / KMART

NA06KOH 002 ACTIVE NATIONAL KOHL'S

NA06KOH 002 ACTIVE NATIONAL KOHL'S

NA08CFA 002 ACTIVE NATIONAL #N/A

NA47GMS 002 ACTIVE NATIONAL GOLDMAN SACHS

NA47GMS 002 ACTIVE NATIONAL GOLDMAN SACHS

NA77BIO 002 ACTIVE NATIONAL #N/A

NA77BIO 002 ACTIVE NATIONAL #N/A

NA77BIO 002 ACTIVE NATIONAL #N/A

NA77BIO 002 ACTIVE NATIONAL #N/A

NA77BIO 002 ACTIVE NATIONAL #N/A

NA77BIO 002 ACTIVE NATIONAL #N/A

NA77BIO 002 ACTIVE NATIONAL #N/A

NA77BIO 002 ACTIVE NATIONAL #N/A

NA77BIO 002 ACTIVE NATIONAL #N/A

Dear Team,

I have an issue in Vlookup, i am doing vlookup in my laptop but it is taking same values for all, please help me how to salve. The below is the example.

Site ID Site ID with job numbers

ZRI584 J-ZRI584-0000115

ZRI584 J-ZRI584-0000115

ZRY072 J-ZRI584-0000115

ZRW958 J-ZRI584-0000115

ZRW958 J-ZRI584-0000115

ZR3106 J-ZRI584-0000115

ZRU170 J-ZRI584-0000115

ZHMA003 J-ZRI584-0000115

ZRS996 J-ZRI584-0000115

ZRS996 J-ZRI584-0000115

ZJE043 J-ZRI584-0000115

ZRH682 J-ZRI584-0000115

ZRU176 J-ZRI584-0000115

ZRS857 J-ZRI584-0000115

ZRU173 J-ZRI584-0000115

ZJE026 J-ZRI584-0000115

ZJD484 J-ZRI584-0000115

Iwant to use a range name in lookup, but use a cell reference to a formula that defines the range name. For instance:

Cell u5 =LOWER(CONCATENATE("f",H5,I5)) returns range name fws

and cell v5 =VLOOKUP($W5,fws,1,1) contains cell reference to u5 so I don't have to enter the range name fws in the lookup formula

but it doesn't work. Is there any easy fix?

Trying to use VLOOKUP with cells that contains both text and numbers.

Example: AU0002

I tried changing to general, text, and number and still will not work with the VLOOKUP.

Formula: =VLOOKUP(A2,[THRESHOLDITEMLISTING5918.xlsx]Sheet1!$A$2:$G$818,1,FALSE)

Stephanie:

I don't think the cell format is the issue.

These are my first thoughts to check.

Check to be certain the data you want VLOOKUP to use as a reference is in the current sheet's A2 cell.

Is this current workbook's name THRESHOLDITEMLISTING5918.xlsx?

If not, can you access this Thresholditemlistings workbook?

Does it require permissions you don't have?

Does Sheet 1 of that Thresholditemlistings workbook contain the list of info you're referencing?

Is the data in A2 through G818?

Is the data in the first column of sheet 1?

Hi Svetlana,

In my case the error was because of a simple whitespace. Make sure that the value in the lookout column didn't have any white spaces. To remove any white space Just select all the values and hit Ctr+H and replace " " with just "". Took quite a while to figure out.

When changing the column from 10 to 11, the formula returns #REF. There is a value in column 11 and it's formatted exactly the same as column 10. The formula works from columns 1-10, but does not work from column 11 to the end of the spreadsheet. Can it not look farther than column 10?

Hi there,

When trying to do a Vlookup to another file my it is not allowing me to even select the table array I want to search. why is this happening?

A note on the #NAME explanation above, where you say you must have misspelled the FUNCTION name. This is not necessarily true, other circumstances where this error might include:

- Where you misspelled the name of ANOTHER function (eg a nested function you're using to produce the lookup value)

- Where you misspelled the "Exact Match" argument, eg providing "F" instead of "FALSE"

I am using Microsoft Excel on a Mac. I've used VLOOKUP for years in very complicated models with no issues. I am now stumped: I have a simple table that works for VLOOKUP for all words except those that begin with "Se" up until words that begin with "Si". So if I look up values that go with Sammy, it's fine, with Stan or Tiger, all fine, but it just returns zeroes if I look up values that go with Selma, Shark, Siam, etc. What gives?

Ugh, sorry for the bother, I found the issue. I had corrected the spelling of scullcap from skullcap in the table but hadn't resorted it.

I am having trouble with my Vlookup after I had got it working. I'm not sure if the list is just too big or formatted wrong. I have it in the left most column searching a different sheet. I'm updating a mailing list with a list I already have and pulling from unique numbers that are between 5 and 6 digits. For whatever reason these digits are no longer working for the vlookup. I have tried to make them text and number and it just won't pull any data no matter how small I make the vlookup. Even just 3 cells that do contain the numbers. I'm am at a loss. I can't make it work, even though it should work.

=Vlookup(B2,Sheet1!B2:Sheet1!F10,2,FALSE) even with "TRUE" it isn't working. I tried Hlookup as well. (I have a list of over 35k, should I just make smaller lists?)

Is it because of the numbers being between 5 and 6 digits?