Contents
Why is lookup formula not working?
Problem: The lookup column is not sorted in the ascending order. If the range_lookup argument is set to TRUE—and one of your lookup columns is not sorted in the ascending (A-Z) order—you will see the #N/A error. Solution: Change the VLOOKUP function to look for an exact match.
Why is VLOOKUP returning the formula and not the value?
It appears that the cell containing the VLOOKUP formula is incorrectly formatted as Text when the formula is entered. Consequently, the “formula” is interpreted as text, not a formula. Simply changing the cell format does not immediately change the type of cell contents. The “formula” is still interpreted as text.
When can VLOOKUP go wrong?
By default, VLOOKUP will do an approximate match. This is a dangerous default because VLOOKUP may quietly return an incorrect result when it doesn’t find your lookup value.
Why does lookup return the wrong value in Excel?
The lookup is just trying to find the identifier and then return the quantity for that identify from the next column. The formula I’m using is “=IFERROR (LOOKUP (B1,Sheet1!$D$2:$E$31,Sheet1!$E$2:$E$31), “”)”. But it keeps either returning no number or the wrong number.
Why is the VLOOKUP formula incorrectly formatted as text?
The problem seems to be the cell that contains the VLOOKUP formula, not the data that is in the “Item Listing at 30th may 2013.xlsx” workbook. It appears that the cell containing the VLOOKUP formula is incorrectly formatted as Text when the formula is entered. Consequently, the “formula” is interpreted as text, not a formula.
What to do when VLOOKUP returns formula instead of value?
VLOOKUP sometimes returns the formula in the cell instead of the value. I resolve it by formatting the cell as General or Number depending on what I’m looking up, then click fx again to reopen the function arguments box, then click OK.
When to use false or true in lookup formula?
I use FALSE most of the time inside the formula because most of the time the data is not sorted, BUT if you already know your data is sorted, you can use TRUE. lookup_value Required. The value to search in the first column of the table or range. The lookup_value argument can be a value or a reference.