Forum Discussion
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 and my data source is showing that invoice type, where am I going wrong please?
11 Replies
Try to compare them manually, type in any empty cell like =A10=IT!B20. If FALSE the could be different type (number and text), extra spaces, something like this.
SarahBrimblecombe Excel doesn't find an exact match of the invoice type. Thus, resulting in an error that you captured with IFERROR(..........,"0")
There could be a mismatch if your invoice types ar numbers in one table and texts in the other. Or, if they are both texts, leading or trailing spaces can also cause a mismatch. For instance, "Type A" is not equal to "Type A " (note: space added behind A).
By the way, is it really your intention to display zero as text when the formula returns and error? If not, remove the quote-marks. Then it will return the number zero.
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
SarahBrimblecombe Difficult to diagnose without seeing the file. Alignment of text should not affect VLOOKUP.
