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 curren...
Formulas & Functions
Need Help
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