Questions about nested xlookup.

%3CLINGO-SUB%20id%3D%22lingo-sub-1531261%22%20slang%3D%22en-US%22%3EQuestions%20about%20nested%20xlookup.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531261%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20nested%20xlookup%20to%20populate%20a%20cell%20based%20on%20two%20tested%20cells.%20They%20are%20defaulting%20to%20%23NA%2C%20but%20I'd%20like%20to%20have%20them%20populate%20as%20blank%20if%20one%20or%20both%20cells%20are%20false%20but%20when%20I%20try%20to%20change%20the%20if_not_found%20of%20one%20or%20both%20it%20gives%20me%20a%20%23value%20error.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20clean%20up%20my%20spreadsheet%20to%20make%20it%20look%20a%20little%20easier%20to%20read%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1531261%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531293%22%20slang%3D%22en-US%22%3ERe%3A%20Questions%20about%20nested%20xlookup.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531293%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F732792%22%20target%3D%22_blank%22%3E%40Scomousa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20a%20little%20difficult%20to%20follow%20exactly%20what%20your%20current%20formula%20must%20look%20like%2C%20as%20well%20as%20what%20you%20want%20to%20be%20blank.%20So%20if%20what%20I'm%20going%20to%20suggest%20doesn't%20make%20sense%2C%20please%20return%20and%20post%20a%20copy%20of%20the%20spreadsheet%20itself....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'd%20suggest%20is%20surrounding%20your%20existing%20formula%20with%20an%20IFERROR%20function%2C%20so%20it%20would%20look%20something%20like%20this%20%3DIFERROR((%3CEM%3Ewhatever_you_already_have%3C%2FEM%3E)%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531319%22%20slang%3D%22en-US%22%3ERe%3A%20Questions%20about%20nested%20xlookup.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531319%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F732792%22%20target%3D%22_blank%22%3E%40Scomousa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20XLOOKUP%20returns%20%23VALUE%20error%20that%20most%20probably%20means%20that%20lookup%20array%20and%20return%20array%20are%20of%20different%20size.%20Why%20so%20-%20hard%20to%20say%20without%20seen%20sample%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1531375%22%20slang%3D%22en-US%22%3ERe%3A%20Questions%20about%20nested%20xlookup.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1531375%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%2C%20this%20worked%20like%20a%20charm!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I'm using nested xlookup to populate a cell based on two tested cells. They are defaulting to #NA, but I'd like to have them populate as blank if one or both cells are false but when I try to change the if_not_found of one or both it gives me a #value error. 

 

How can I clean up my spreadsheet to make it look a little easier to read?

3 Replies
Highlighted

@Scomousa 

 

It's a little difficult to follow exactly what your current formula must look like, as well as what you want to be blank. So if what I'm going to suggest doesn't make sense, please return and post a copy of the spreadsheet itself....

 

What I'd suggest is surrounding your existing formula with an IFERROR function, so it would look something like this =IFERROR((whatever_you_already_have),"")

Highlighted

@Scomousa 

If XLOOKUP returns #VALUE error that most probably means that lookup array and return array are of different size. Why so - hard to say without seen sample file.

@mathetes 

 

Thank you, this worked like a charm!