Index is typically preferable to vlookup, given the advantage of being able to be used as a reference itself (e.g. (Uncommon, albeit parsimonious lookup tool)ģ] Index =INDEX(D4:D6,MATCH(G4,DATEVALUE(C4:C6),0)) Here are two other methods to achieve this objective:Ģ] Sum =SUM(1*(DATEVALUE(C4:C6)=G4)*(D4:D6)) I suspect the fundamental goal is to return the cell corresponding to the datevalue of the text you're looking up - you couldn't be fussed whether it's a vlookup or any other form of lookup. This is why you can lookup datevalue of the single column which contains valid text formatted dates, but once you reference a larger range, datevalue(larger range) will return errors for any value in that range which cannot be converted to a date. M2-M3, offset etc.) vlookup cannot be used as a reference itself - it requires the entire range to be included and then 'pivots' on the first column. Revision : see here for how to input array formula for different versions of Excel - these are otherwise valid for both 2010/Office 365 compatible version of Excel.ġ] Vlookup =VLOOKUP(G4,IFERROR(DATEVALUE(C4:D6),D4:D6),2,0) Of course you can- you just need to know how vlookup works and manipulate accordingly - I provide the solution you have specifically requested, together with 2 other possible ways of looking up without 'parsing to date format' in first instance:
0 kommentar(er)
