INDEX MATCH get next match

[audan2009]

Hello,

Working on this formula:

=INDEX(INDIRECT($F$36&"!EB:EB"),MATCH(LOB!G41,INDIRECT($F$36&$G$36),0))),"")

Recently found out that INDEX MATCH will return the 1st value it matches. In this case it is looking up LOB!G41 in column DD!J:J but there are 3 occurrences of the value 75,000 and each belong to a different SKU. So, it's just returning the 1st one. Those are in column EB. I also have a column A, that has the row number.

I can't figure out how to make this find the next result. So, 75,000 - 1, 75,000 - 2 and 75,000 3.

I figure its some use with SMALL and Countif but I can't figure it out

I was looking at this formula in this thread:

http://www.excelforum.com/excel-form...-function.html

If someone can tell me how to adjust this formula and what its supposed to mean, I think we can figure it out!

[audan2009]

=INDEX(INDIRECT($F$36&"!EB1:EB196"),SMALL(IF($G$41:$G$50=G41,ROW(INDIRECT($F$36&"!EB1:EB196"))-ROW($EB$1)+1),COUNTIF(G$41:G41,G41)))

This is what I'm testing

[audan2009]

Oh my god this works:

=INDEX(INDIRECT($F$36&"!EB:EB"),SMALL(IF(INDIRECT($F$36&$G$36)=G47,ROW(INDIRECT($F$36&"!EB:EB"))-ROW($EB$1)+1),COUNTIF(G$41:G47,G47)))

[audan2009]

There's something wrong with that formula. After I started using it, excel would not save. Spent the last 5 hours trying to repair the damage....

[audan2009]

Reverted back to:

Please Login or Register  to view this content.

[audan2009]

I can't even "Save As" another name. I can open and save the same file. This is insane.