Forum Discussion

SarahBrimblecombe's avatar
SarahBrimblecombe
Copper Contributor
Jun 08, 2020

Formula not returning result

I am trying to look up a column and return with the description. The formula I am using is =IFERROR(VLOOKUP([@[INVOICE_TYPE]], IT!A$1:B$80, 2, FALSE),"0") however when I hit enter it comes up with 0 ...
excel
Formulas and Functions
SarahBrimblecombe's avatar
SarahBrimblecombe
Copper Contributor
Jun 08, 2020

Riny_van_Eekelen I found that my text was aligned to the left in one table and in the centre in the other. So the top line of the table has now worked, however when I drag down the rest of the table it shows 0 still. I am getting a message inconsistent calculated column formula

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jun 08, 2020

SarahBrimblecombe Difficult to diagnose without seeing the file. Alignment of text should not affect VLOOKUP.

    • Riny_van_Eekelen's avatar
      Riny_van_Eekelen
      Platinum Contributor
      Jun 08, 2020

      SarahBrimblecombe As suspected, the invoice types in the large table are texts where all the invoice types in the IT sheet are a mix of numbers and texts.

       

      Now, I believe that your real table comes from a query, so you need to do the correct data typing there or change the data type in the IT sheet to match your table.

       

      EDIT: Just formatting the first column on the IT sheet is not enough. Easiest would be to enter, e.g. in C2

      =LEFT(A2,2)

       Copy it all the way down and then copy /paste values column C back to column A. Then your original formula should work.

Resources