Okay. Here is a screen shot of the data, the actual workbook is included as attachment. The sheet is rather messy, sorry. It is a work in the initial stages.
Here is a screen shot of the worksheet. column A is a reference or catalogue number, column B is the value of the item in column A. In column G the reference number is entered, this also the number that is used in the lookup function that is in column H. The expected return is the number corresponding to the reference number. In column F an x marks the incorrect returns for the lookup value entered.
The sheet was created to list food items and calorie values in columns A, B respectively. So, when a food item is entered in column G, the return is the correct calorie number associated with the food item. For example, A2 is 1/2 cup of oats and B2 defines the total calories as 150.
When an incorrect value was returned, I entered 0s for all of the blank values in column B. Then I checked formatting, copy and paste error (because I used the same formula for all of column H), then I checked the value entered with a true/false function, then I rewrote the function to include only 200 values in the lookup and return columns (I wasn't sure if there was a limit to the lookup function).
Not sure what else to try.
