Home » Windows » Top 5 Reasons Why VLOOKUP is Not Working and How to Fix it Guaranteed

Top 5 Reasons Why VLOOKUP is Not Working and How to Fix it Guaranteed

Posted on: | By: – 223 Comments
Vlookup is not working

Working as a ‘Spreadsheet Guy’ i use VLOOKUP every day and a lot of people comes to me with an issue saying “my vlookup is not working”, for one reason or another. VLOOKUP is a very common excel function (in fact the 3rd most used Excel Function). You have to admit, as much fun using VLOOKUP is, so frustrating are the errors associated with it. This is because of the limitations of VLOOKUP. Things will get easier in the future as Microsoft has announced XLOOKUP as a replacement for VLOOKUP. In this article, we are going to discuss 5 top reasons why your VLOOKUP is not working and how to fix them.

Types of Errors in VLOOKUP

Before we can solve the error it is important to know which kind of error you are getting and what can be the possible reasons for that error type. Excel VLOOKUP formula gives 3 main types of errors. Below are the 3 types of errors and what they possibly mean.

1. #VALUE!

This Error is the least common Error. The #VALUE! error occurs in two scenarios

  1. The lookup_value argument is more than 255 characters.
  2. The col_index_num argument contains text or is less than 0.

2. #REF!

The REF! stands for reference. Excel displays this error when a formula references a cell that no longer exists, usually caused by deleting the cells that formula is referring to. You can also notice this error when your table does not have that much range as mentioned in the col_index_num argument. You can solve this by fixing your col_index_num argument.

3. #N/A

This is the most common type of error in excel VLOOKUP function and probably the reason why your VLOOKUP is not working. N/A means Not Available and Excel gives this error when the lookup_value you are looking for is not found in the first column of the table_array of the VLOOKUP. Below we will discuss the top reasons why #N/A is showing up and how to fix it.

Top Reasons Why Your VLOOKUP is Not Working, and How to Fix Them

1. Numbers Formatted as Text

This is the most common cause of the #NA error When the lookup_value is a Number and the Table_array has the value formatted as Text, VLOOKUP can not search for the value. This scenario occurs when someone is trying to show the leading Zeros. To fix this issue all Numeric values must be formatted as Numbers. vlookup is not working-Number stored as Text

Solution: The cells containing Numeric values as text can be distinguished by a filled triangle on the Top-Left corner of the cell. Click on that cell and you can find a Caution Symbol, click on it and choose Convert to Number. This will also work on a cell range.Convert Text to Number

Also, you can Multiply your range by 1 to convert it to Numbers. Just write 1 in any blank cell and copy it, then select your range and press CTRL+ALT+V and select Multiply operation.Multiply Text by 1

2. Table Array is not Reference Locked

When you are using VLOOKUP for a range and you drag down the formula to copy it down. But now only half of the formulae are working and you wonder why? As you can see in the example below you can see as you copy your formula down with each cell the Table_array is also shifting downReference not locked

Solution: After selecting the table_array in your formula press F4 to Reference lock your table_array before you copy your formula down. It is a good practice.Reference Locked

3. Leading or Trailing Spaces at the End of the Value

Look at the example below, the formula and data look absolutely correct yet it is showing #NA error and the VLOOKUP is not working 100 percent. The Problem here is invisible to the naked eye. To see this go to the lookup value and put your cursor at the end in the formula bar, you can see some leading and trailing spaces in your lookup value. The VLOOKUP formula doesn’t work with extra spaces.Trailing Spaces

Solution: To clean your data by removing the extra useless spaces you can use the TRIM command. The trim command removes all the leading and trailing spaces of your value. You can use the TRIM command in separate dummy sheet and once your data set is trimmed you can Copy-Paste it as Values to its destination. OR you can change your formula as

=VLOOKUP(TRIM(Lookup_Value), Table_Array, Col_Index_Num,[Range_Lookup])Result of Trim

4. The Numbers are Fraction

It is wise to use VLOOKUP only with Integers. The VLOOKUP will show #NA error when one of the lookup_value or Table_array value is a fraction. The value might look like an Integer because of the Number Formatting but you can check the actual Fraction in the formula bar. See the example below.Fraction Number

Solution: To Get rid of the Fractions from your Data set, you can use the ROUND function. ROUND function coverts the fractions to the Integer value. Once you Round up your range you can copy-paste it back as values. OR you can write up your formula as

=VLOOKUP(ROUND(Lookup_Value,0), Table_Array, Col_Index_Num,[Range_Lookup])

Fraction Result

5. Change of Calculation Option:

If all your Formulae are correct and none of them are working even after spending hours debugging the formula you might want to take a look at the Calculation Options. Sometimes the calculation options can get set to ‘Manual’ and stop all your formulae to work automatically. To reset it to Automatic go to Formula tab click on the Calculation option and set it to Automatic.Calculation Options

Formula Tab → Calculation Options → Automatic

So these were the top reasons why your VLOOKUP formula is not working. If you found it useful please mention in the comments below. Also, suggest in comments if you have some more ideas.

Thank you!

 

Related Articles

What is Excel XLOOKUP Function and How to Use XLOOKUP?

What is Excel XLOOKUP Function and How to Use XLOOKUP?

Shortcut Keys and Methods to Rename Files on Windows 7/8/8.1/10

Shortcut Keys and Methods to Rename Files on Windows 7/8/8.1/10

223 Replies to “Top 5 Reasons Why VLOOKUP is Not Working and How to Fix it Guaranteed”

  16. Some are medicines that help people when doctors prescribe. Everything what you want to know about pills.
    https://stromectolst.com/# ivermectin lotion cost
    Comprehensive side effect and adverse reaction information. Medicament prescribing information.

    Reply

  26. Prescription Drug Information, Interactions & Side. Cautions.
    https://mobic.store/# can you buy cheap mobic without rx
    Some are medicines that help people when doctors prescribe. drug information and news for professionals and consumers.

    Reply

  55. Приветствую, мы осуществляем ремонт в помещении для процедур лечения пиявками. Скажите какая расценка механизированной полусухой стяжки и расценки на Прочный пол для постройки из ЛСТК? Какую толщину механизированной стяжки пола можно делать в здании? Как правильно посчитать состав и стоимость? Кстати, по роду деятельности приходится часто ездить. В связи с этим вот полезный совет: отлично экономить топливо помогают бензиновые карты. Думаю пригодится. Снижение операций по недвижимости. Вследствие снижения спроса на недвижимость и спроса на земельные участки под новые проекты девелоперы стали осторожнее подходить к покупке участков под строительство, что уже привело к уменьшению по итогам минувшего года операций в этом срезе на 15-20 %. Инвестировать в покупку земель хотят в основном фирмы, повысившие прибыль на фоне ажиотажных реализаций, и местные игроки. Но их стоимостные ожидания часто расходятся с ценой предложения, поясняют специалисты. На этом фоне количество вложений в участки в текущем году удержится скромным.

    Reply

  72. Tired hiring and training developers?
    Try https://iconicompany.com

    Professioal developers for your Business.
    Stop hiring full-time developers! Hire independent contractors instead!

    For businesses, the availability of especially skilled
    developers helps a company to respond to economic instability,
    boosting their workforce when they need it most and making
    it easier to access hard-to-find skills

    Reply

  74. Hello everybody!

    My name is Dasha, I’m 23 years old, I’m from Latvia, I want to tell you interesting observations from life)
    I was skeptical about everything magical: spells, love spells, spoilage, etc., watched Harry Potter and laughed at these weirdos ….
    I studied these issues on the website https://www.zagovorna.ru
    But one time a friend offered to argue, and held a small ceremony, it was incredible, but it worked, it was directed at my body so that I felt aroused to her for the next week, and what do you think?
    I couldn’t sleep, my nipples were constantly standing up and my whole body wanted to go to visit her!
    As a result, I admitted that the conspiracies work, and she turned off this energy effect.
    So be careful and don’t deny what you haven’t studied in practice)

    Good luck to everyone!

    Reply

  97. Hi,
    nice to collect you! Give permission me introduce myselft
    29 yr old Early Youth (Pre-Primary School) Teacher Jewell from Pine Falls, has several hobbies and interests that include baseball, and tea tasting. Feels travel a revealing experience after building a journey to Fernando de Noronha and Atol das Rocas Reserves
    https://peatix.com/user/10478431

    Reply

  101. In order for caviar to be stored as long as possible, it must initially be fresh
    After opening, even in the refrigerator, caviar can lie for a certain time – as a rule, no more than 3 days
    There is an option to purchase black caviar not in a jar, but by weight
    When defrosting, instead of expensive caviar, an incomprehensible porridge with a fishy smell may appear on the festive table
    At home, black caviar is optimally placed in a glass container.
    https://fokachos.com/

    Reply

  121. Read information now. Definitive journal of drugs and therapeutics.
    cheap cialis
    Prescription Drug Information, Interactions & Side. Definitive journal of drugs and therapeutics.

    Reply

  129. Здравствуйте, друзья! Вследствие уменьшения спроса на недвижимость и спроса на земельные участки под новые застройки компании стали аккуратнее подходить к приобретению земельных участков под новое строительство, что уже привело к уменьшению по результатам минувшего года договоров в этом сегменте на 15-20 %. Вкладывать в покупку площадок могут чаще всего застройщики, увеличившие прибыль на фоне ажиотажных реализаций, и региональные игроки. Но их ценовые требования зачастую разнятся со стоимостью предложения, объясняют консультанты. На этой волне размер финансовых вложений в землю в этом году удержится небольшим. А мы делаем ремонт каркасного для дома. Подскажите какова цена полусухой стяжки для ЛСТК и стомость подоконника из массив сосны? какова толщину механизированной полусухой стяжки можно делать в клинике? Ещё, я так понимаю, полусухая стяжка расслаивается, поверхность сильно глубоко растрескалась в локальных местах. Подобный тип повреждений подлежит ремонту, однако нужно будет применять специализированные строительные составы. Каким образом скалькулировать материалы и расценки на – Системы контроля доступа (домофоны, замки , турникеты и тд)? С наилучшими пожеланиями

    Reply

  136. Добрый день, товарищи!

    Cтяжка пола  (полусухая) , даже если она была смонтирована четко в соответствии с грамотной инструкцией, с течением времени портится. Это неудивительно, ведь она испытывает на себе колоссальные нагрузки от массы полового покрытия, предметов, передвижения людей и прочих динамических воздействий. В связи с чем для устранения полной потери ее эксплуатационных качеств периодически необходимо выполнять ремонт стяжки пола. О характеристиках стяжки нужно узнать, когда вы мониторите цены постройки из легких стальных конструкций либо других материалов.

    Для того чтобы иметь представление об уровне повреждения пола, для начала надо совсем удалить старое покрытие, освободить поверхность от мусора, пыли и грязи. Далее надо определиться с видом деформации и ремонта, который надо будет осуществить.

    Может произойти один из нижеследующих случаев:

    Гладкая поверхность имеет неглубокие ямки и немного трещин, повышен уровень появления пыли. Данные изъяны не опасны, они несложно устраняются при минимальной затрате времени и финансов.
    Стяжка расслаивается, поверхность довольно глубоко потрескалась в локальных участках. Такой вид повреждений подлежит восстановлению, но надо будет задействовать специализированные строительные смеси.
    Поверхность существенно деформирована, пол покрыт сильными щелями. Ремонт деформаций подобной силы доступен лишь профессионалам, владеющим определенными знаниями и техникой.

    Reply

  149. “선입금&예약금없는 후불제
    시그니처출장마사지 시그니처출장안마 입니다.
    자택 오피스텔 모텔 호텔 등 에서 이용가능하며
    경기 인천 서울 전지역 30분 이내
    방문 가능합니다” 출장안마

    Reply

  152. “이제 나가지말구 간편하게 계신곳에서 편하게 이용하세요 ~
    서울 전지역 모텔 . 호텔 . 오피스텔 . 원룸 등에서
    전화한통만 주시면 ok ~ !!
    직접 방문해서 케어 해드립니다 ” 출장안마

    Reply

  167. Amusement Park Kathmandu
    Kathmandu Park is located in one of the most popular areas of Mallorca – in the southwestern resort of Magaluf. It is built in the form of an upside-down Tibetan-style house and is filled with all sorts of modern technologies, immersing you in a world of fantastic interactive adventures.
    https://nanatoriya.de/

    Reply

  172. Приглашаем Ваше предприятие к взаимовыгодному сотрудничеству в сфере производства и поставки Пруток 58НХВКТБЮ и изделий из него.

    – Поставка катализаторов, и оксидов
    – Поставка изделий производственно-технического назначения (чаши).
    – Любые типоразмеры, изготовление по чертежам и спецификациям заказчика.

    00b07e7

    Reply

  174. Hello everybody!
    I’m Masha, I’m 32, I live in England, I raise 2 children who go to school)
    School is quite a lot of stress, both for children and for parents, and constant lessons and preparation for tests in specialized subjects drove Me crazy (
    I became nervous, stopped sleeping with my husband and a nervous tic began, it was terrible…
    It’s good that my friends advised me to find sites with solutions, and try to do homework according to their methodology.
    By the way, a good website https://www.controlworks.ru
    There are no ads, convenient search and a lot of valuable information about test papers!
    To be honest, I began to sleep peacefully, sex and peace in the family were restored, thanks to such sites where you can find solutions and devote more time to your favorite things!
    Good luck!

    Reply

  176. Hello everybody!
    My name is Yana, I live in Sweden, a beautiful blonde as it should be for Swedish women)
    I had children, it was time for school assignments and lessons and the real horror began ((
    Constant lack of sleep, nerves, checks and assessments at school..
    I even stopped sleeping with my husband, I’m just not in the mood. A well-known friend advised websites with homework, where you can find answers and quickly solve lessons, and then give yourself to your favorite activities!
    By the way, a good website https://www.hometask.ru
    There are no ads, convenient search and a lot of valuable information about test papers!
    To be honest, I began to sleep peacefully, sex and peace in the family were restored, thanks to such sites where you can find solutions and be free!
    Good luck!

    Reply

  178. Приглашаем Ваше предприятие к взаимовыгодному сотрудничеству в сфере производства и поставки РљСЂСѓРі ниобиевый НБЦ-1 и изделий из него.

    – Поставка порошков, и оксидов
    – Поставка изделий производственно-технического назначения (обруч).
    – Любые типоразмеры, изготовление по чертежам и спецификациям заказчика.

    00b07e7

    Reply

  181. Приглашаем Ваше предприятие к взаимовыгодному сотрудничеству в направлении производства и поставки Тяжелые сплавы вольфрама и изделий из него.

    – Поставка концентратов, и оксидов
    – Поставка изделий производственно-технического назначения (тигли).
    – Любые типоразмеры, изготовление по чертежам и спецификациям заказчика.

    3c49d30

    Reply

  184. Приглашаем Ваше предприятие к взаимовыгодному сотрудничеству в сфере производства и поставки 42НХТЮА – ГОСТ 10994-74 и изделий из него.

    – Поставка карбидов и оксидов
    – Поставка изделий производственно-технического назначения (блины).
    – Любые типоразмеры, изготовление по чертежам и спецификациям заказчика.

    fb6_617

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *