Search code examples
exceloffice365ms-officedata-analysislookup

LOOKUP in excel showing #N/A for the next year date

I have month-wise cumulative data.

data Now i am trying to LOOKUP the month value then divide with number of days according to the dates. Here when the date of the year changes I am getting #N/A error. Please guide me. Thanks.

formula I used was: =LOOKUP(MONTH(B6),MONTH($B$2:$K$2),$B$3:$K$3)/DAY(EOMONTH(B6,0))

I have got the result after using alternate formula like HLOOKUP & SUMIFS but I am curious to know why LOOKUP value not worked.

Solution

  • Lookup requires the lookup range to be sorted ascending. Your range goes {8,9,10,11,12,1,2,3,4,5}

    Important: The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.

    From https://support.microsoft.com/en-us/office/lookup-function-446d94af-663b-451d-8251-369d5e3864cb

    So what is happening is that when the month it is looking up is less than the first month in the lookup range it stops at the first and returns an error because it assumes the first value in the lookup range to be the lowest value in the lookup range.

    LOOKUP will not work in this setup. XLOOKUP will be a better option.

    If you are using Microsoft 365, use XLOOKUP - it's not only faster, it also lets you search in any direction (up, down, left, right).

    Also from that link above

    =XLOOKUP(MONTH(B6),MONTH($B$2:$K$2),$B$3:$K$3/DAY(EOMONTH(B6,0)))