XLOOKUP columns

%3CLINGO-SUB%20id%3D%22lingo-sub-2816576%22%20slang%3D%22en-US%22%3EXLOOKUP%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2816576%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20the%20latest%20version%20of%20Excel%20Office%20365%20on%20mac.%20When%20using%20xlookup%2C%20Excel%20does%20not%20allow%20me%20to%20add%20a%20whole%20column%20to%20the%20formula.%20Adding%20cells%20works%20just%20fine.%20Why%20is%20that%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2816576%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2816598%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2816598%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1176823%22%20target%3D%22_blank%22%3E%40R-Z_S%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20give%20bit%20more%20details%2C%20what%20exactly%20do%20you%20mean%20under%20adding%20column%20to%20the%20formula.%20Better%20with%20some%20sample.%3C%2FP%3E%0A%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2816997%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2816997%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20understand%20that%20XLOOKUP%20is%20a%20formula%20in%20Excel%20used%20for%20pulling%20together%20related%20information.%20Now%20I%20am%20taking%20a%20course%20in%20LinkedIn%20and%20in%20this%20part%20I%20am%20learning%20about%20formulas%20and%20XLOOKUP%20which%20is%20one%20the%20most%20used%20formulas%20replacing%20VLOOKUP%20and%20HLOOKUP.%20In%20the%20tutorial%2C%20the%20teacher%20is%20using%20an%20example%3A%20associating%20employee%20ID-numbers%20(column%20G)%20with%20employee%20names%20(column%20H).%20He%20shows%20how%20to%20add%20a%20whole%20column%20containing%20names%20into%20the%20function.%20For%20this%20he%20writes%20%22%3Dxlookup(%22%20and%20then%20to%20add%20the%20desired%20value%20which%20is%20the%20ID%23%20he%20wants%20to%20match%20with%20an%20employee%20he%20clicks%20in%20the%20cell%20where%20the%20ID%23%20resides.%20This%20makes%20the%20formula%20looking%20like%20%22%3Dxlookup(G2%22%20after%20which%20he%20adds%20a%20comma.%20Then%20he%20looks%20for%20the%20lookup%20array%20and%20in%20this%20case%20the%20ID%23%20are%20in%20a%20column%20called%20C.%20To%20add%20the%20whole%20column%20into%20the%20formula%20he%20clicks%20on%20the%20C%20which%20is%20the%20name%20of%20that%20column.%20When%20ge%20does%20this%20the%20formula%20looks%20like%20%22%3Dxlookup(G2%2CC%3AC%22.%20Now%2C%20when%20I%20click%20on%20C%20I%20get%20a%20pop%20up%20message%20instead%20reading%20%22There%20is%20a%20problem%20with%20the%20formula.%20Not%20trying%20to%20type%20a%20formula%3F%20...%22%20going%20on%20explaining%20what%20to%20do%20if%20you%20are%20not%20typing%20a%20formula%2C%20which%20I%20am.%20So%20for%20some%20reason%20Excel%20365%20for%20Mac%20does%20not%20acknowledge%20my%20typing%20on%20the%20column%20C%20as%20part%20of%20writing%20a%20formula.%20Why%20is%20that%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I am using the latest version of Excel Office 365 on mac. When using xlookup, Excel does not allow me to add a whole column to the formula. Adding cells works just fine. Why is that?

6 Replies

@R-Z_S 

Could you please give bit more details, what exactly do you mean under adding column to the formula. Better with some sample.

Thank you.

@Sergei Baklan I understand that XLOOKUP is a formula in Excel used for pulling together related information. Now I am taking a course in LinkedIn and in this part I am learning about formulas and XLOOKUP which is one the most used formulas replacing VLOOKUP and HLOOKUP. In the tutorial, the teacher is using an example: associating employee ID-numbers (column G) with employee names (column H). He shows how to add a whole column containing names into the function. For this he writes "=xlookup(" and then to add the desired value which is the ID# he wants to match with an employee he clicks in the cell where the ID# resides. This makes the formula looking like "=xlookup(G2" after which he adds a comma. Then he looks for the lookup array and in this case the ID# are in a column called C. To add the whole column into the formula he clicks on the C which is the name of that column. When ge does this the formula looks like "=xlookup(G2,C:C". Now, when I click on C I get a pop up message instead reading "There is a problem with the formula. Not trying to type a formula? ..." going on explaining what to do if you are not typing a formula, which I am. So for some reason Excel 365 for Mac does not acknowledge my typing on the column C as part of writing a formula. Why is that? 

Thanks in advance!

@R-Z_S 

Not sure. Do you click on entire column like here

image.png

after that type comma and click on another column value from which shall be returned?

image.png

Yes, and when clicking on entire column I get the error message.

@R-Z_S 

If an error like this

image.png

something is wrong with syntaxes. Perhaps you shall use semicolons instead of commas - depends on your regional settings.

@Sergei Baklan Just found out through trial and error that semicolon is the correct input.