Share via

The Index, Small, and Row Functions

Anonymous
2022-02-24T17:49:56+00:00

I was able to use Index, Small, and Row functions together nicely to get the information I was looking for. However, I have another set of criteria that I was wondering how to fit into the formula. For instance, I have a worksheet set up that I type in a single finished good part number to show me all the components that go into the finished good. Like, =INDEX(*range,*SMALL(IF(cell=*range,*ROW(range)-ROW(cell)+1),ROW(1:1))). Is there a way to have this formula perform another logical test to match country of origin to only display part numbers that match with selected countries?

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2022-02-24T23:44:00+00:00

    Hi,

    This should work

    =INDEX(*range,*SMALL(IF(cell=*range,IF(another_cell=country_range,*ROW(range)-ROW(cell)+1)),ROW(1:1)))

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2022-02-24T18:34:42+00:00

    Hello Jared,

    To your description shared, I would like to request you to share the sample data of your workbook and required output, so we can share the formula that matches your situation correctly.

    Note: Please upload the workbook to your OneDrive or any cloud location and share the link with us.

    Best Regards,

    Mia

    1 person found this answer helpful.
    0 comments
  3. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2022-02-25T23:51:55+00:00

    Hi,

    Share some data and show the expected result.

    0 comments
  4. Anonymous
    2022-02-25T14:02:35+00:00

    Hi Ashish!

    The example above gave me the error message that said, "You've entered too few arguments for this function." It then jumps my cursor to the SMALL( closing bracket. I haven't been able to figure out what that means. Excel won't let me evaluate the formula at all to see what I am missing.

    I should also add, I played around with using the match function in an array formula. I was able to get it to work, but the data didn't quite match exactly. I am sure there is a very simple piece that I am missing somewhere. :)

    0 comments