Hi Everyone,
I am new to this forum and trying to learn Xlookup going through Mynda Treacy's XLOOKUP example file. I tried to create the following simple formula for '=XLOOKUP(G5,Table4[Category],XLOOKUP(G6,Table4[Product],Table4[[Sales]:[Rating]])) for looking up Clothing and Caps but it's churning out a #Value! error message
It many be a simple syntax error or formatting error, and i spent a lot of time to figure our the error in vain. Attached is the practice excel file. Please help me
Hi and welcome to our forum! It's great to see you're practicing XLOOKUP.
In this scenario you would only lookup Caps. There's no need to factor in the clothing category too. Therefore, the formula would be:
=XLOOKUP(G6,Table4[Product],Table4[Sales])
Mynda
Thanks Mynda for clarifying. Meanwhile i am trying to follow your HR Dashboard exercise and encountered an issue of the pivot table not picking up the full csv. file data. Therefore i am making it as an another Question.
This may help of other newbees like me trying to learn Excel in O365
John
I hope you don't mind me chipping in, John.
I took a look at your file and would like to offer the following
If you want to show the formula you used in, say, cell H6, you can put this in cell I6 =FORMULATEXT(H6) then whenever you change the formula in H6, FORMULATEXT will update automatically ...
Since you seemed to want the flexibility of returning the Clothing Category and then the Products in that category, I did this:
In cell G9 I entered the following =UNIQUE(FILTER(C2:C29,(B2:B29="Clothing"))) which tells me which are the unique Products in the Clothing range
I then set up Data Validation for Products in cell G6 by referring to G9#
Finally, my formula in cell H6 is then =XLOOKUP(G6,Table4[Product],Table4[[Sales]:[Rating]]), which SPILLs to I6 since you asked for two outputs and my FORMULATEXT() is then in J6
I have attached my file for your review!
