Please answer the following question for me. There are two Excel sheets, and I need to determine which accounts are matched in Sheet 1's Column A. Sheet 2 is the reference.

I'm trying to find a formula that will tell me every account on sheet 1 that corresponds to the position number. Sheet 2 contains the solution. Could someone please assist?

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

Position Account
5001 41150100, 41150101,41200000
5031 43010101, 43256856
5051 78589545, 89659845
Account Position
41150100 5001
41150101 5001
78589545 5051

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

Feb 10 in Others 50 views

## 1 answer to this question.

You can try the following (formula 1) assuming there are no Excel version restrictions according to the tags provided in the question:

```=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: • 59,740 points

## Excels INDEX with MATCH for multiple criteria

How to Use INDEX MATCH With Multiple Criteria ...READ MORE

## Crawling through multiple excel files, match and copy data to master file

One application only. It would be quicker ...READ MORE

## What do Clustered and Non-Clustered index actually mean?

I want to know about Clustered and Non clustered indexes. ...READ MORE

## Excel - IF combined with Index Match

Try: =IFERROR(IF(ISNUMBER(O2),VLOOKUP(A2,Sheet2!\$C\$2:\$E\$10,3,FALSE),""),"") =IFERROR(IF(ISNUMBER(O2),INDEX(Sheet2!\$E\$2:\$E\$10,MATCH(A2,Sheet2!\$C\$2:\$C\$10,0)),""),"") where the first "" means that O2 is not a number, ...READ MORE

## INDEX MATCH formula in Excel returning incorrect results in some cells and correct results in others

Step through the formula that produces the ...READ MORE

## INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX(\$I\$2:\$I\$26,MATCH(V4,\$R\$2:\$R\$26,0))) The calculation ...READ MORE