Forum Discussion

Hope Wallis's avatar
Hope Wallis
Copper Contributor
Dec 10, 2017

need help with a vlookup/sumif formula

Hello,

 

I'm trying to build a formula that will allow me to do a vlookup/sumifs using a lookup value with a "zero" as the last character of the code/lookup value.  However, the formula is currently ignoring the zero.  So I get the same result for 12345.13 and 12345.130, but they are two different "codes" with different corresponding lookup values in my table array.  I can't seem to find anything that works.  Does anyone have a suggestion?

 

Thank you.

Formulas & Functions
Need Help

2 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Dec 10, 2017

    If these numbers are treated as actual numbers, it is normal to get the same result!

    There is no difference between this 12345.13 and this 12345.130, since the https://en.wikipedia.org/wiki/Trailing_zero to the right of a decimal point, as in 12.3400, do not affect the value of a number and may be omitted.

     

    It's just a mathematical rule!

     

    If you want the VLOOKUP to differentiate between this 12345.13 and this 12345.130, you have to treat the last one as text!

     

    Just put an apostrophe ( ' ) behind it as follows:

    '12345.130

    Then hit Enter, and the apostrophe will be hidden!

    • Hope Wallis's avatar
      Hope Wallis
      Copper Contributor
      Dec 10, 2017

      Thanks for the response Haytham.  That didn't work though.  Its the formula that is turning the text into a number, regardless of how the "string" is being formatted in the sheet.

       

      I actually did some more research and found that a sumproduct formula does the trick.  The formula below is the basis for what I used, although I had to add another criteria selection to the first part of the formula.  Worked like a charm.  

       

      =SUMPRODUCT(--($A$1:$A$1000=A1),$B$1:$B$1000)

Resources