conditional INDEX MATCH

[oli35]

Hello guys,

I'm using INDEX MATCH formula to find a number in another table, but it always returns the first number it finds. I want it to return a number based on a variable.

I want it to do this: if a count number on the corresponding row is equal to 1, I want the INDEX MATCH to find the second number in the table it finds, not the first one. If the count number is equal 2, I want it to find the third number in the table, If the count number is equal 3, I want it to find the fourth number, etc.

Is this possible?

Thanks

 

[ghrain22]

=index(lookuplist,small(if(lookupvalue=lookuplist,row(lookuplist),""),1),column,areanum)

enter as an array: ctrl+shft+enter

The bolded 1 returns the smallest value in that list. if it was a 2 then it would return the 2nd smallest

 

[ghrain22]

=index(lookuplist,small(if(lookupvalue=lookuplist,row(lookuplist,"")),1),column,areanum)

enter as an array: ctrl+shft+enter

The bolded 1 returns the smallest value in that list. if it was a 2 then it would return the 2nd smallest

So you could theoretically replace that 1 with a count if function:
=index(lookuplist,small(if(lookupvalue=lookuplist,row(lookuplist),""),Countif(lookuplist,"="&lookupvalue),column,areanum)

 

[oli35]

Thanks for the reply.

I wasn't clearn though it's hard to explain.

I have a data table with bank accounts with transactions. I want a function to match numbers in that table, then once a number has been found once, I don't want it to match the same number again. I want it to look in the transactions, then find the next matching number. So if it finds 100$, the next time it finds 100$ I don't want it to match the one that was already found. The transactions are not sorted by numbers, but by date, so I don't think SMALL will work, correct?

 

[ghrain22]

You could/would still use the small function, but you would need a cell for each resulting match.

After you return the appropriate numbers what do you plan on doing with them? Summing them? Because then a sumif function(or some other methodology) would be more suitable for this.

 

[Aladin Akyurek]

@oli35

Perhaps you could post a small sample along the desired results?