excelvlookuppowerquery

Trouble with Vlookup on power queries?

So it's a simple function,

=vlookup(a23,sheet3!a:e,5,0)

I have two vlookup both returning from a power query, one works and the other doesn't both are formatted in exactly the same way and both query's are very similar (no obvious differences)

Query one (Sheet 2)

Query Two (Sheet 3)

Formulas

As you can see the vlookup returns values from sheet3 but not sheet2 even though there are differences. I have re-written it many times and changed the 0 - false in the formula with no changes.

Solution

  • Unfortunately, your screenshot shows only the formulas, not the error messages. Errors are important when troubleshooting.

    Check the data types. It looks as if the data in column A in the second screenshot is numeric, whereas column A in the first screenshot definitely contains text, as can be seen by the trailing / signs in some cells. Ensure that the lookup value and the first column of the lookup table are the same data type. Numbers may look like numbers, but could be stored as text. Non-matching data types will cause the #N/A error.