Forum Discussion

choongko's avatar
choongko
Brass Contributor
Sep 11, 2021
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...
Formulas and Functions
  • choongko's avatar
    choongko
    Sep 11, 2021
    I see... Tks.
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 11, 2021

choongko 

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.

 

choongko's avatar
choongko
Brass Contributor
Sep 11, 2021
Thanks. Appreciate it.

Resources