Hello Everyone,
Could you please assist me in finding one single a nested xlookup formula to search "if value not found" in the next sheets or workbooks.
I have attached the excel workbook for your reference.
Thank you in advance for your time and kindness.
Ricardo
Hello,
No file attached.
Br,
Anders
Thank you Mr. Sehlstedt for notifying me.
I uploaded it now.
Ricardo
Hi Ricardo,
It's inefficient to nest XLOOKUP like this:
=XLOOKUP(C11,'Ambient 1'!$E$3:$E$10,'Ambient 1'!$F$3:$F$10,XLOOKUP(C11,'Ambient 2'!$E$3:$E$10,'Ambient 2'!$F$3:$F$10, etc...)
Instead, you can use the new VSTACK function to stack the arrays from the individual sheets like so:
=XLOOKUP(C11,VSTACK('Ambient 1:Chilled'!$E$3:$E$10),VSTACK('Ambient 1:Chilled'!$F$3:$F$10),0)
Mynda
Thank you Mynda for the tip :),
I will try it and will give you my feedback 🙂
Hello Mynda,
Thank you, big time, for the prompt assist 🙂 it worked amazingly, and I was able to add some multiple criteria on the formula to all the variables checked; meaning if I have the same SKU but from different country of origin and different packing, etc...
Here is what the final formula looked like:
=XLOOKUP(A8&B8&D8&E8,VSTACK('Dry 1:Chilled'!$A$6:$A$87)&VSTACK('Dry 1:Chilled'!$B$6:$B$87)&VSTACK('Dry 1:Chilled'!$D$6:$D$87)&VSTACK('Dry 1:Chilled'!$E$6:$E$87),VSTACK('Dry 1:Chilled'!$K$6:$K$87),0)
Again, thank you a million for your generous input and positive knowledge sharing.
Wishing you all a blessed day/times.
Ricardo
So pleased it was helpful, Ricardo!
