Index and Match multiple matches

0 votes

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, 2023 in Others by Kithuzzz
• 38,010 points
408 views

1 answer to this question.

0 votes

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: 

excel output

answered Feb 10, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer

Excels INDEX with MATCH for multiple criteria

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

answered Nov 8, 2022 in Others by gaurav
• 23,260 points
652 views
0 votes
1 answer

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

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

answered Jan 12, 2023 in Others by narikkadan
• 63,720 points
441 views
0 votes
1 answer
0 votes
0 answers

What do Clustered and Non-Clustered index actually mean?

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

Jun 22, 2022 in Others by nisha
• 2,210 points
241 views
0 votes
1 answer

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

answered Nov 17, 2022 in Others by narikkadan
• 63,720 points
563 views
0 votes
1 answer
0 votes
1 answer

Excel worksheet multi-criteria function (like Index-Match array) to fetch last value of an item, looking up by item name and date

Use: =SUMIFS(C:C,B:B,E12,A:A,MAXIFS(A:A,B:B,E12)) It will return the value at the ...READ MORE

answered Apr 6, 2023 in Others by narikkadan
• 63,720 points
556 views
0 votes
1 answer

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

answered Oct 8, 2022 in Others by narikkadan
• 63,720 points
742 views
0 votes
1 answer

Excel Formula multiple Index Match and Average the result

If the conditions are separate and unrelated, ...READ MORE

answered Oct 31, 2022 in Others by narikkadan
• 63,720 points
2,897 views
0 votes
1 answer

Excel formula that combines MATCH, INDEX and OFFSET

Try this: =INDEX((OFFSET(SheetA!F:F,0,SheetA!A1)),MATCH(SheetB!C4, ...READ MORE

answered Nov 7, 2022 in Others by narikkadan
• 63,720 points
1,728 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP