
Active Member

December 5, 2022

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

VIP

Trusted Members

December 7, 2016



July 16, 2010

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

Answers Post

Active Member

December 5, 2022

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
1 Guest(s)
