• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index match for ranges

Hello,

In column E3 I have a Indexmatch formula. It's indexing column A to match E1, which is the value of zero.

In column F3 I have an Indexmatch formula. It's indexing column A to match F1, which is the value of zero.


In columnG, H, I, and J I have different ranges that I would l like to create an indexmatch formula.

I want the indexmatch formulas to match all the products in column A with the respective numbers or ranges in E1, F1, G1, H1, I1, J1.

I need help with the formula that adjusts the index matches. For example, In E4 the next match would be Baiwei A4, etc.

I also need help with writing a formula for indexmatch that accounts for ranges. For example, G1 is a range from 2 - 4. The formula needs to account for a match of ranges between 2-4. Thank you
 

Attachments

  • indesmatch ranges.xlsx
    27.2 KB · Views: 5
@shadedlight Let me shed some light on this ;)

I think INDEX MATCH formula based approach to extract all products with given value or value range in row 1 is going to be very hard. I suggest using pivot tables to get the output you want. It won't be in the format you need, but it will have everything you need. You can then re-arrange this data using some other method or consume it as-is.

See attached workbook. The key is to just group your value column based on the criteria you need. Just select all the values you want in one group (for example for the group 2-4 you would select 2,3 and 4 and right click and say group).

Hope that helps.
 

Attachments

  • indesmatch ranges.xlsx
    27.2 KB · Views: 5
Back
Top