Hopefully I am overthinking this but I am trying to do the following –
I have pulled the top 10 items sales by code using XLOOKUP and LARGER. I now, based on the sales number pulled, look up the product name associated with that code. The code can changed (it’s a drop down list) so I need that column reference to move. All the numbers are being pulled from another sheet which has every code and their sales for all products.
Inputs
Output
So far I tired an XLOOKUP within an XLOOKUP and INDEX/MATCH. I think there’s a way to use OFFSET or INDIRECT but so far its spitting out #VALUE or #NA
Run4fun is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
0
I’m not sure whether “inputs” (from the other sheet) is from a query or entered in that sheet. If feasible, I would suggest inserting a table for the “inputs” (adding a name for the item column). With table added,
- we could define a name for the code drop-down – for example
code_for_summary
Then get the summary with
=TAKE(SORT(
CHOOSECOLS(Table1,XMATCH(HSTACK("Item",code_for_summary), Table1[#Headers])),2,-1),10)
I can propose you this solution:
[Sheet2!B4] =TAKE(SORT(CHOOSECOLS(Sheet1!A2:E13,1,MATCH(C1,Sheet1!B1:E1,0)+1),2,-1),10)
=TAKE(
SORT(
CHOOSECOLS(
Sheet1!A2:E13,
1,
MATCH(C1,Sheet1!B1:E1,0)+1
),
2,
-1
)
,10
)
Algo:
- MATCH determines the sales column number.
- CHOOSECOL choses the first and (1) columns.
- SORT sorts (2) by the second column, which is the selected sales column, by the descending order.
- TAKE takes the first 10 rows from (3).
One formula calculates everything.