Problem with Excel, the VLOOKUP() function

Symptom:

I have a lookup formula on one tab that references data on another tab. First cell that has lookup formula works correctly. Next formula down the column returns wrong value. Below are the formulas.





Raw gas Lift Volume

=LOOKUP(A2,'April Data'!B$2:B$28,'April Data'!E$2:E$28)
=LOOKUP(A3,'April Data'!B$2:B$28,'April Data'!E$2:E$28)
=LOOKUP(A4,'April Data'!B$2:B$28,'April Data'!E$2:E$28)


Hypothesis:

1 - The third argument of the VLOOKUP function is a number (or a calculation that returns a number) representing the column number from within the table that data is returned. You have a range reference ('April Data'!E$2:E$28) as your third argument instead a number for the column.

2 - If this data is text then you should use a fourth option argument, FALSE, in the VLOOKUP. If the data is numerical then the reference table needs to be sorted in ascending order or the VLOOKUP will return incorrect results.


Procedure:

Here is what the first few rows of the spreadsheet tab that has the vlookup formula on it looks like. I tried to put in some row and column references. This tab is labeled apr adj.



The lookup formula in cell B2 tries to find a match for the data in cell A2 (1-4) (tab apr adj) on the tab April Data in the range B2:B28 and return the corresponding value from the range E2:E28. It returns the correct value of 11160.

The lookup formula in cell B3 tries to find a match for the data in cell A2 (2-4) (tab apr adj) on the tab April Data in the range B2:B28 and return the corresponding value from the range E2:E28. It returns the incorrect value of 5487 rather than the correct value of 4650.


I thought that the third argument of the vlookup function could be a range. The help screen in Excel for the vector form of lookup shows the following:

LOOKUP(lookup_value,lookup_vector,result_vector)
Would ('April Data'!E$2:E$28) not be a result vector?

The lookup_value (tab apr adj cell A2 etc)and lookup_vector (tab april data range B2:B28) both contain general type data. The result_vector data (tab apr adj range E2:E28) is numeric. The range of data that is being searched is sorted by the lookup_vector.


Result:

Assume you have data in A1:B5. A1 has the first well, with a name of "1-4". Next to that well name in B1 is its volume. A2 has the next well "name" of "2-4" and in B2 is its volume, etc.

Assume further that you type a well name of "1-4" in D1 and in E1 you want to return that well's volume. Here is the formula:

=VLOOKUP(D1,$A$1:$B$5,2,False)

The number 2 represents which column from the table ($A$1:$B$5) to get the data from when there is well name match.