# conditional INDEX MATCH

#### oli35

##### New Member
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

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
=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

Last edited:
=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)

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?

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.

@oli35

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

Replies
12
Views
444
Replies
3
Views
445
Replies
5
Views
230
Replies
0
Views
359
Replies
6
Views
228

1,221,059
Messages
6,157,679
Members
451,432
Latest member
Rizzy73

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back