Forum Discussion

Sotiris Moschou's avatar
Sotiris Moschou
Copper Contributor
Dec 14, 2017

TRUE vlookup brings the wrong results

Hello to all,   I wanted to make my full-of-vlookups workbook faster so I thought I would start using vlookups(TRUE) on a sorted table since I read that its faster that way.   Problem is that...
excel
Formulas and Functions
vlookup
JoB_19's avatar
JoB_19
Copper Contributor
Jan 05, 2019

Having the very same issue using TRUE  I have used FALSE to get an "exact" match for each row, but the results are #NA.  I think this might be a formatting issue for the data sheet.  Or maybe the problem with the Page ID number.

 

=VLOOKUP(A2,costing!$A$2:$B$3000, 2, FALSE)

 

Appreciate any help!

JoB_19's avatar
JoB_19
Copper Contributor
Jan 07, 2019

Found the solution!!!  To be honest, my sister did!  Teamwork is great!

 

This formula fixed the issue.

 

=VLOOKUP(A2&" *",costing!$A:$B,2,FALSE)

 

Hope this helps others at the Microsoft board.

  • TJD22's avatar
    TJD22
    Copper Contributor
    Jan 09, 2025

    THANK YOU! I was using VLOOKUP to locate matching company names between sheets, but some of the names would include "Inc." etc. as the data was pulled from different sources. My first attempt only pulled exact matches, but adding the &"*" added the different versions of the company names. You are a life saver!

  • kirstygibson's avatar
    kirstygibson
    Copper Contributor
    Feb 18, 2020

    Thank you saved my data analysis!!! Was about to give up...but didn't

  • JoB_19's avatar
    JoB_19
    Copper Contributor
    Jan 08, 2019

    Updating by taking out the space before the *.

     

    =VLOOKUP(A2&"*",costing!$A:$B,2,FALSE)

     

    Let me know if this helps with your VLOOKUP.

    • Gaynor2022's avatar
      Gaynor2022
      Copper Contributor
      Nov 23, 2022

      JoB_19 

      thank you for this but am lost @costing. FALSE vlookup is giving me wrong results even though the values are there on the table.

    • Sotiris Moschou's avatar
      Sotiris Moschou
      Copper Contributor
      Jan 08, 2019

      Thanks for that
      Before I test it though,
      doesn't the fact that you are using 'FALSE' beat the original purpose of using 'TRUE'?

      I mean the reason I chose 'TRUE' (as it's also shown on the original post) was to speed things up but it didn't work (ie. I got the wrong results).

      Are you saying that by adding an asterisk to a 'FALSE' vlookup speeds things up like when using 'TRUE'?

      I'm obviously missing something here :)

      • JoB_19's avatar
        JoB_19
        Copper Contributor
        Jan 08, 2019

        Maybe I misunderstood your goal using TRUE.  You mention "speed things up"; not sure what you mean by that.  Were you looking for approximate matches?

         

        Using TRUE gives you approximate matches .  Using FALSE gives you exact matches.

         

        (see link below for a detailed explanation from Microsoft)

         

        Quick Reference Card by Microsoft.  https://support.office.com/en-us/article/quick-reference-card-vlookup-refresher-750fe2ed-a872-436f-92aa-36c17e53f2ee

         

        For my project, I was looking for exact matches; therefore FALSE would show correct data!  In order to correct my issue needed to add the wild card asterisk to eliminate "hidden" leading or trailing spaces or characters in my part number column.

         

         

         

         

         

Resources