Forum Discussion
Solved
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 ach...
- I see... Tks.
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.
- I see... Tks.
- Thanks. Appreciate it.