I have looked for answers but can't get my head round this.
I want to use a value in a cell on a Calculator worksheet to lookup the same value in a cell in a column on a Data worksheet and return a value from a cell on the same row of the Data worksheet. The lookup value is a simple month year reference eg 04/20 for April 2020. I then want to drag this lookup formula down it's column of the Calculator worksheet so the reference number on each row looks up the same value on the Data worksheet and returns a value from the appropriate row. Sounds like out of the box Vlookup, but it doesn't work because neither the reference values on the Calculator worksheet nor the values on the Data worksheet can be in ascending order (the data is input by others from a management system and the order can't be changed eg by a sort). The returned value is monetary and can be any value including zero and negatives. Both the lookup and the data orders are randomised. So Vlookup returns either duplicate values, zeros or #N/A errors. I have tried using True instead of False in the formula but this understandably returns errors, so Vlookup would not appear to be an option.
I have tried using Index/Match, but this returns the same value multiple times. What is interesting is that the value it returns for the first row of the Calculator worksheet is not the correct value for the corresponding Data worksheet. Even though the first reference number on the Calculator worksheet is 03/18, and is coincidentally the reference number for the first row on the data worksheet, the formula is returning the data for 02/18 which is on the sixth and last row of the Data worksheet. But 02/18 does not occur on the Calculator worksheet at all. So the formula would appear to be looking for the lowest value reference even though it is not being asked to! Even though other lookup references on the Calculator worksheet are present on both the Calculator and Data worksheets, (eg 04/18, 06/18 etc) they all return the value for 02/18.
I don't know if this is relevant, but the reference on the Calculator is a formula, looking up data from another column based on a rule..
The formula in the Calculator worksheet is: INDEX('DATA'!$T$11:$T$59,MATCH($M16,'DATA'!$C$11:$C$59).
The value I want returned is in column T of the Data worksheet. The lookup value is in M16 of the Calculator worksheet, the lookup range (containing the mm/yy data) in the Data worksheet is C11:C19. The values to be returned are in the range T11:T59.
It had occurred to me that either the Vlookup or Index/Match solutions could work if there were two helper columns that could automatically order the data in Column C and T of the Data worksheet so that they were in ascending order (ie the first helper column would automatically order the data in column C so that Vlookup would work, but the data in the second column would also have to reorder the data in Column T to match). I don't know if this is feasible!
Many thanks
HughT
I want to use a value in a cell on a Calculator worksheet to lookup the same value in a cell in a column on a Data worksheet and return a value from a cell on the same row of the Data worksheet. The lookup value is a simple month year reference eg 04/20 for April 2020. I then want to drag this lookup formula down it's column of the Calculator worksheet so the reference number on each row looks up the same value on the Data worksheet and returns a value from the appropriate row. Sounds like out of the box Vlookup, but it doesn't work because neither the reference values on the Calculator worksheet nor the values on the Data worksheet can be in ascending order (the data is input by others from a management system and the order can't be changed eg by a sort). The returned value is monetary and can be any value including zero and negatives. Both the lookup and the data orders are randomised. So Vlookup returns either duplicate values, zeros or #N/A errors. I have tried using True instead of False in the formula but this understandably returns errors, so Vlookup would not appear to be an option.
I have tried using Index/Match, but this returns the same value multiple times. What is interesting is that the value it returns for the first row of the Calculator worksheet is not the correct value for the corresponding Data worksheet. Even though the first reference number on the Calculator worksheet is 03/18, and is coincidentally the reference number for the first row on the data worksheet, the formula is returning the data for 02/18 which is on the sixth and last row of the Data worksheet. But 02/18 does not occur on the Calculator worksheet at all. So the formula would appear to be looking for the lowest value reference even though it is not being asked to! Even though other lookup references on the Calculator worksheet are present on both the Calculator and Data worksheets, (eg 04/18, 06/18 etc) they all return the value for 02/18.
I don't know if this is relevant, but the reference on the Calculator is a formula, looking up data from another column based on a rule..
The formula in the Calculator worksheet is: INDEX('DATA'!$T$11:$T$59,MATCH($M16,'DATA'!$C$11:$C$59).
The value I want returned is in column T of the Data worksheet. The lookup value is in M16 of the Calculator worksheet, the lookup range (containing the mm/yy data) in the Data worksheet is C11:C19. The values to be returned are in the range T11:T59.
It had occurred to me that either the Vlookup or Index/Match solutions could work if there were two helper columns that could automatically order the data in Column C and T of the Data worksheet so that they were in ascending order (ie the first helper column would automatically order the data in column C so that Vlookup would work, but the data in the second column would also have to reorder the data in Column T to match). I don't know if this is feasible!
Many thanks
HughT
