Log In
Register
Home
Active Member
December 5, 2022
Offline
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
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hello,
No file attached.
Br,
Anders
Active Member
December 5, 2022
Offline
Thank you Mr. Sehlstedt for notifying me.
I uploaded it now.
Ricardo
July 16, 2010
Offline
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
Offline
Thank you Mynda for the tip :),
I will try it and will give you my feedback 🙂
Active Member
December 5, 2022
Offline
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)
