@ XLookup returning #N/A error in 2 cells, works on all other cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1950788%22%20slang%3D%22en-US%22%3E%40%20XLookup%20returning%20%23N%2FA%20error%20in%202%20cells%2C%20works%20on%20all%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1950788%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20using%20xlookup%20function%2C%20the%20formula%20looks%20up%20the%20current%20month%20(formula%20in%20cell%3A%26nbsp%3B%20%3Dtext(eomonth(today()%2C-2)%2B1%2C%22mmmm%22)%20to%20look%20up%20in%20a%20row%20of%20months%3A%26nbsp%3B%20Oct%2C%20Nov%2C%20Dec%2C....September%2C%20and%20return%20the%20corresponding%20total%20commissions%20for%20the%20month.%26nbsp%3B%20The%20results%20appear%20correctly%2C%20with%20the%20exception%20of%20November%20(col.%20D)%20and%20July%2C%20(Col%20L).%26nbsp%3B%20For%20those%20two%20months%2C%20it%20returns%20%23N%2FA.%26nbsp%3B%20I%20don't%20know%20why%20this%20is%20happening.%26nbsp%3B%20I%20have%20checked%20the%20months%20have%20the%20%22mmmm%22%20custom%20format.%26nbsp%3B%20Can%20you%20help%3F%3C%2FP%3E%3CP%3EMy%20formula%20is%3A%26nbsp%3B%20xlookup(C29%2CC2%3AN2%2CC28%3AN28)%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bxlookup(month%2C%20range%20of%20months%2C%20range%20of%20commissions).%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1950788%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1952114%22%20slang%3D%22en-US%22%3ERe%3A%20%40%20XLookup%20returning%20%23N%2FA%20error%20in%202%20cells%2C%20works%20on%20all%20other%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1952114%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F889098%22%20target%3D%22_blank%22%3E%40Watson11%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20your%20data%20in%20C2%3AN2.%20That%20could%20be%20extra%20space%20like%20%22November%20%22%20or%20something%20similar.%20If%20month%20names%20are%20entered%20manually%2C%20type%20January%20in%20C2%20and%20drag%20it%20till%20N2%2C%20all%20names%20will%20be%20added%20correctly.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

In using xlookup function, the formula looks up the current month (formula in cell:  =text(eomonth(today(),-2)+1,"mmmm") to look up in a row of months:  Oct, Nov, Dec,....September, and return the corresponding total commissions for the month.  The results appear correctly, with the exception of November (col. D) and July, (Col L).  For those two months, it returns #N/A.  I don't know why this is happening.  I have checked the months have the "mmmm" custom format.  Can you help?

My formula is:  xlookup(C29,C2:N2,C28:N28)

                         xlookup(month, range of months, range of commissions).


Thanks in advance!

 

1 Reply

@Watson11 

Please check your data in C2:N2. That could be extra space like "November " or something similar. If month names are entered manually, type January in C2 and drag it till N2, all names will be added correctly.