Using INDEX, MATCH and COUNTIFS to filter and match data in Excel. Formula incorrect?

0
votes

I am trying to use INDEX, MATCH and COUNTIFS in excel to filter for relevant data within 2 spreadsheets. However, the formula below only returns the first value of per the matched criteria and replicates this value throughout the identified rows:

IF(COUNTIFS(A:A,$A43,J:J,G$3),INDEX(L:L,MATCH(G$3,J:J,0))),"")

*Please see a sample of the criteria i am trying to match below. If the IDs and Names in table 1 and 2 are matched, display the % Allocated in Table 2. The problem i'm having is that the formula only matches Bill at 10% and displays 10% for every other matched name.

    Table 1:            

    ID     Name    Project % Allocated
    4121    Bill    30100   10%
    4122    Murray  30200   30%
    4123    Harmon  30300   50%
    4132    John    30408   40%


    Table 2:            

    ID     Name    Project % Allocated
    4121    Bill    30100   
    4122    Murray  30200   
    4131    Shauna  30407   
    4132    John    30408
2
excelindexingmatchcountif
I am afraid that this is how index match works. It looks for the first match and returns an index. There are more complex formulas available that will allow you to get a full list but they are a step up from regular index match and will require more information of your data. – a-burge
Are you entering it as an array formula? – Tom
No i am not, please kindly see above for more detail on the data . – laureen85

2 Answers

0
votes

If ID is unique to both lists vlookup will work.

In Table2 % Allocated formula:

=vlookup(A2,Table1!A:D,4,0)

If unique combination is ID and Name as you state this array formula will work.

=INDEX(Table1!D:D,Match(A2&B2,Table!A:A&Table1!B:B,0))

Be sure to press Ctrl + Shift + Enter when exiting cell edit mode.

assume that Table1 and Table2 are on two separate sheets (named accordingly) in column A:D

0
votes

Make a reference to the row number using row () +1. This will make it so that the range it is looking for becomes smaller and smaller as it works its way down the list. This will make it a dynamic formula. Can you show a picture of what you are trying to do. Right now the idea isnt all that tangible.