SOLVED

Use cell-adress as reference when Xlookup not found

%3CLINGO-SUB%20id%3D%22lingo-sub-2028863%22%20slang%3D%22en-US%22%3EUse%20cell-adress%20as%20reference%20when%20Xlookup%20not%20found%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2028863%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20i%20use%20reference%20to%20a%20cell%20value%20when%20Xlookup%20is%20not%20found%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20returns%20an%20error%20(%23Value)%3C%2FP%3E%3CP%3E%3DXlookup(U2%23%3BKonteringsbilag!K%3AK%26amp%3BKonteringsbilag!J%3AJ%26amp%3BKonteringsbilag!S%3AS%3BKonteringsbilag!O%3AO%3B%3CSTRONG%3E%24Y%242%3C%2FSTRONG%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20works%20perfect%3C%2FP%3E%3CP%3E%3DXlookup(U2%23%3BKonteringsbilag!K%3AK%26amp%3BKonteringsbilag!J%3AJ%26amp%3BKonteringsbilag!S%3AS%3BKonteringsbilag!O%3AO%3B%3CSTRONG%3E%22Not%20found%22%3C%2FSTRONG%3E)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2028863%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-2030540%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20cell-adress%20as%20reference%20when%20Xlookup%20not%20found%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2030540%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGeir%2C%20you%20need%20to%20push%20evaluation%20for%20the%20reference%2C%20formula%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3DXlookup(U2%23%3BKonteringsbilag!K%3AK%26amp%3BKonteringsbilag!J%3AJ%26amp%3BKonteringsbilag!S%3AS%3BKonteringsbilag!O%3AO%3B%3C%2FSPAN%3E%3CSTRONG%3E%24Y%242%3CFONT%20color%3D%22%23FF0000%22%3E%26amp%3B%22%22%3C%2FFONT%3E%3C%2FSTRONG%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3Eshall%20work%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2046330%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20cell-adress%20as%20reference%20when%20Xlookup%20not%20found%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046330%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%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%3Bperfect.%20It%20worked.%20I%20ended%20up%20with%20using%20IFERROR%2C%20if%20the%20Xlookup%20got%20an%20error%2C%20I%20selected%20value%20from%20a%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2046949%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20cell-adress%20as%20reference%20when%20Xlookup%20not%20found%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2046949%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Regular Contributor

Hi,

 

Can i use reference to a cell value when Xlookup is not found?

 

This returns an error (#Value)

=Xlookup(U2#;Konteringsbilag!K:K&Konteringsbilag!J:J&Konteringsbilag!S:S;Konteringsbilag!O:O;$Y$2)

 

This works perfect

=Xlookup(U2#;Konteringsbilag!K:K&Konteringsbilag!J:J&Konteringsbilag!S:S;Konteringsbilag!O:O;"Not found")

 

Best regards

- Geir

3 Replies
best response confirmed by Geir Hogstad (Regular Contributor)
Solution

@Geir Hogstad 

Geir, you need to push evaluation for the reference, formula like

=Xlookup(U2#;Konteringsbilag!K:K&Konteringsbilag!J:J&Konteringsbilag!S:S;Konteringsbilag!O:O;$Y$2&"")

shall work

 

 

Thank you @Sergei Baklan perfect. It worked. I ended up with using IFERROR, if the Xlookup got an error, I selected value from a cell.

 

- Geir