• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Help with Index Match

abhi0300

New Member
I have a sheet (attached) where I need to extract the values with Index Match and then find the corresponding dates.

Please see the file for understanding. Any help is appreciated.
 

Attachments

Peter Bartholomew

Well-Known Member
This kind of reverse lookup can be done relatively routinely using 365.
Code:
= LET(
  m, ROWS(active),
  n, COLUMNS(active),
  k, SEQUENCE(m*n),
  r, 1+QUOTIENT(k-1,n),
  c, 1+MOD(k-1,n),
  x, IF(INDEX(active,r,c)=@Cust,INDEX(InvNo,r),""),
  y, IF(INDEX(active,r,c)=@Cust,INDEX(Dates,c),0),
  d, IF({1,0},x,y),
  FILTER(d, y>0) )
Unpivotting and filtering are not so straightforward in older Excel versions. In future, the Lambda function will allow the user to develop a formula and then pass a parameter to it so that the same formula can be used in multiple places.
Code:
= LAMBDA(cst,
   LET(
     m, ROWS(active),
     n, COLUMNS(active),
     k, SEQUENCE(m*n),
     r, 1+QUOTIENT(k-1,n),
     c, 1+MOD(k-1,n),
     x, IF(INDEX(active,r,c)=cst,INDEX(InvNo,r),""),
     y, IF(INDEX(active,r,c)=cst,INDEX(Dates,c),0),
     d, IF({1,0},x,y),
   FILTER(d, y>0) )
  )(@Cust)
Using a defined name, this then becomes
Code:
= DETAILS(@Cust)
74050
 

Peter Bartholomew

Well-Known Member
A formula that could be ported back to traditional Excel might be
Code:
= LET(
  n, COUNTIFS(active, @Cust),
  k, SEQUENCE(n),
  filtered, IF(active=@Cust, coordinates, ""),
  v, SMALL(filtered,k),
  IF({1,0}, INDEX(InvNo, QUOTIENT(v,1000000)), MOD(v,1000000)))
 

abhi0300

New Member
A formula that could be ported back to traditional Excel might be
Code:
= LET(
  n, COUNTIFS(active, @Cust),
  k, SEQUENCE(n),
  filtered, IF(active=@Cust, coordinates, ""),
  v, SMALL(filtered,k),
  IF({1,0}, INDEX(InvNo, QUOTIENT(v,1000000)), MOD(v,1000000)))
Thank you so much Peter, it seems that you have this working. I, however, am not much versed with these functions and as of now I am unable to use these solutions on my file. What is the range that is to be defined as 'Cust' and how do I use these formulas...through VBA or directly.. Could you please bear with me and let me know the process.
 

Peter Bartholomew

Well-Known Member
The main factor is the version of Excel that you are using. I am not sure the Excel community as a whole have realised how radical the change has been from the legacy spreadsheet versions to 365. Although 365 is backward compatible, and so will run a traditionally-developed workbook, there is very little reason to bring such techniques forward when 365 offers a far more comprehensive programming environment.

LET and LAMBDA are just new functions but they open up the possibility of using local names as variables and of passing variables to formulas so that they may be used as functions.

In the attached, I have used defined names to hold intermediate arrays (otherwise, messing around with CSE is a pain) and I think the formulas using SMALL to filter will work on older versions of Excel.

74070
 

Attachments

abhi0300

New Member
Thanks a lot Peter, this is a huge help. Life saved!

I am using 365. In the second sheet, when replace "ABCD" with my actual data (also 4 letters), I get #N/A error in all the tables (LET, ANON LAMBDA, Names LAMBDA, SMALL) however the Filter solution in Sheet 1 keeps on working.

I can live with it, I just wanted to have separate tables for all the customers at once instead of having to filter them one by one. However, if its not too much trouble, could you tell me what I am doing wrong to keep the tables working.

Once again, seriously, thanks a lot.
 

Peter Bartholomew

Well-Known Member
The second sheet picks up the customer name from the list in Column A, @Cust meaning the particular value on the same row as the formula.
The first sheet uses a dropdown called 'selection' which seems to be surviving your updating process better.
The sheet 2 formulas are to be preferred because the calculation is more visible, rather than being tucked away in Name manager.
I have edited references to @Cust to change them to 'selection' which should help.
The Lambda's offer the neatest and most flexible approaches, but are still on beta release.
 

Attachments

Peter Bartholomew

Well-Known Member
The unpivot step is far slicker in PQ. I found it a bit more effort to hold on to the 'day' headings as dates but I am not that fluent using the Power tools.
 

abhi0300

New Member
The second sheet picks up the customer name from the list in Column A, @Cust meaning the particular value on the same row as the formula.
The first sheet uses a dropdown called 'selection' which seems to be surviving your updating process better.
The sheet 2 formulas are to be preferred because the calculation is more visible, rather than being tucked away in Name manager.
I have edited references to @Cust to change them to 'selection' which should help.
The Lambda's offer the neatest and most flexible approaches, but are still on beta release.
Hi Peter,

Despite my best efforts, I am unable to update the names. As soon as I replace ABCD with something else, say, BNPS, everything in the tables in Sheet 2 turns to #N/A which also doesn't get back to normal even if I undo the change.

I just want ONE type of table on Sheet 02 for all the 04 customers displayed at once...nothing else. I know I am asking too much but I am completely at loss here. You've done so much work on this it's a shame I am still unable to use it.

Is there any way to delete all tables from Sheet 2, keep just one and make 4 copies of it?

Many many thanks.
 

Peter Bartholomew

Well-Known Member
From your description, the problems may be due to our using different versions of Excel or at least different update states. Nearly all my Excel formulas start
= LET(
so, if that is not available to you, nothing will refresh correctly. I have taken the sheet 1 formula that you seem to be happiest with and moved it to sheet 2. That particular version of the formula depends heavily on formulas written within defined names so they are central to any debugging and validation exercises.
74118
 

Attachments

abhi0300

New Member
From your description, the problems may be due to our using different versions of Excel or at least different update states. Nearly all my Excel formulas start
= LET(
so, if that is not available to you, nothing will refresh correctly. I have taken the sheet 1 formula that you seem to be happiest with and moved it to sheet 2. That particular version of the formula depends heavily on formulas written within defined names so they are central to any debugging and validation exercises.
View attachment 74118
Peter,

This is exactly what I wanted.

Many many thanks for taking so much time and pain to resolve my issue. You just saved me hours and hours of work!

Greatly appreciated!!!
 
Top