conditional INDEX MATCH

oli35

New Member
Joined
Aug 29, 2014
Messages
6
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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
=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:
Upvote 0
=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)
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top