CODEWITHSUNDEEP
excelindexingmatchformulaxlookup
AshSam123
AshSam123

Reputation: 21

Index and Match multiple matches

I need help with the following query. There are 2 excel sheet and I need to find out in Sheet 1 in Column A what are the different accounts matching, the refernce is Sheet 2.

I am looking for a formula, which can give me all the account in sheet 1 in corressponds to the position nr. The anwser is in sheet 2. Can someone please help?

eg. 5001 = should give me 41150100, 41150101, 41200000

Position Account
5001
5031
5051
Account Position
41150100 5001
41150101 5001
78589545 5051

I am looking for a formula, which can give me all the account in sheet 1 in corressponds to the position nr. The anwser is in sheet 2. Can someone please help?

eg. 5001 = should give me 41150100, 41150101, 41200000

Upvotes: 0

Views: 136

Answers (1)

David Leal
David Leal

Reputation: 6759

Assuming no Excel version constraints as per the tags listed in the question, you can try the following (formula 1):

=LET(pos, A2:A4, accnt, B2:B4, REDUCE({"Account","Position"}, pos, LAMBDA(ac,p,
 VSTACK(ac,LET(f,TEXTSPLIT(@FILTER(accnt,pos=p),,","), HSTACK(f, IF(f=f, p)))))))

Here is the output: excel output

Notes:

  • You would need to clean up your input because in some cases the delimiter is just a comma and in other cases, a space is added.
  • If the question refers to doing it backward, as @ScottCraner suggested in the comments, then assuming the output from the previous screenshot is now the input, then we have (formula 2):
  =LET(acc, D2:D8, pos, E2:E8, ux, UNIQUE(pos), out, MAP(ux, LAMBDA(p,
   TEXTJOIN(",",,FILTER(acc, pos=p)))), HSTACK(ux, out))

formula 1: Uses the REDUCE/VSTACK pattern, check my answer to the question: how to transform a table in Excel from vertical to horizontal but with different length for more details on how to use it. In this case, we use the header to initiate the accumulator.

TEXTSPLIT is used to split the account information by , into rows. We use implicit intersection (@) to convert the FILTER output (array of one element only) into a single string to be able to use TEXTSPLIT, otherwise, it returns the first element only.

We use the condition IF(f=f, p) to generate a constant array with the position value (p). HSTACK is used to generate the output on each iteration in the format we want (first account, then position).

A more verbose formula, but maybe easier to understand, since it doesn't use the VSTACK/REDUCE pattern, could be the following:

=LET(pos, A2:A4, accnt, B2:B4, split, TEXTSPLIT(TEXTJOIN(";",,accnt), ",",";"),
 mult, MMULT(1-ISNA(split), SEQUENCE(COLUMNS(split),,1,0)),
 outP, TOCOL(TEXTSPLIT(TEXTJOIN(";",,REPT(pos&",",mult)),",",";",1),2),
 HSTACK(TOCOL(split,2), outP))

The main idea is to use TOCOL. The name split, generates the array with the account information. The name mult, calculates the number of columns with values. Now we know how many times we need to repeat position values. We use REPT to repeat the value and generate an array via TEXTSPLIT.

Upvotes: 1

Related Questions