Forum Discussion
Trying to integrate ROW function in index and match to retrieve multiple values.
I am having issues understanding the concept of extracting a list of data from a criteria using Excel 2016 and 365. See attach file. I was trying to use index and match, with row function to achieve the result, but was not successful. Appreciate it if some one could provide me the solutions for both versions. You explanation to the solution would be help me greatly.
- I see... Tks.
5 Replies
First the 365
= LET( allOrders, CHOOSE({1,2}, Table1[Order], Table1[InvoiceNo]), FILTER(allOrders, Table1[Company]=Company) )... and then legacy Excel
= ROW(Table1[@]) - ROW(Table1[#Headers]) = IFERROR( SMALL( IF(Table1[Company]=Company, k), k), "") = IF(ISNUMBER(idx), INDEX(Table1[Order], idx), "") = IF(ISNUMBER(idx), INDEX(Table1[InvoiceNo], idx), "" )I have long since forgotten where CSE is needed or where relative referencing will do.
=ROW(Table1[@]) - ROW(Table1[#Headers])
I was going through your formula, I couldn't find the table (Table1[@]) in the table list. What would the formula be if my data is not a ranged table.
Table1 is the range with the green filled header row. Table1[@] is the structured reference notation for the current record of the table and, typed anywhere in line with the table, will return a range reference to a single record. The function ROW( ) defaults to the current row, so it could be used without any cell references.
- Thanks. Appreciate it.
