INDEX & MATCH with 3 Creteria (2 Text & 1 Number column)

**Hasan Khan** · 06-21-2021, 05:01 AM

Hi,

1) I am a retailer, I am purchasing products from different Vendors/WholeSalers in different categories for reselling purpose
2) I want charge different margin on products from different vendors for different categories.
3) And i also want to consider different price range for charging margin for a given vendor in a given category
4) I have used MATCH & INDEX but unable to get the expected results

For. e.g.
Vendor A - Clothes - Price 990 - Margin% = 20%
Vendor A - Clothes - Price 450 - Margin% = 30%
Vendor B - Clothes - Price 990 - Margin% = 15%
Vendor B - Clothes - Price 450 - Margin% = 25%

Please check attached xls file.

**Glenn Kennedy** · 06-21-2021, 08:07 AM

I do not know what Excel version you are using (that, rather than your Office version should be in your profile) ... please amend.

If you are using 365, use this:

=INDEX($D$2:$D$19,MATCH(1,($A$2:$A$19=F2)*($B$2:$B$19=G2)*($C$2:$C$19>=H2),0))

If you are using an earlier version, use this:

=INDEX($D$2:$D$19,MATCH(1,INDEX(($A$2:$A$19=F2)*($B$2:$B$19=G2)*($C$2:$C$19>=H2),0),0))

**Hasan Khan** · 06-28-2021, 02:11 AM

Sorry for delayed reply from my side. And thanks for giving me the solution, I have implemented in my excel and it is working perfectly fine.

And I am using Excel 2007, which i didn't mentioned in my query.

Thanks once again.

**Glenn Kennedy** · 06-28-2021, 02:36 AM

Please update your profile to show Excel 2007.
You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

INDEX & MATCH with 3 Creteria (2 Text & 1 Number column)

LinkBack

Thread Tools

Rate This Thread

Display

INDEX & MATCH with 3 Creteria (2 Text & 1 Number column)

Re: INDEX & MATCH with 3 Creteria (2 Text & 1 Number column)

Re: INDEX & MATCH with 3 Creteria (2 Text & 1 Number column)

Re: INDEX & MATCH with 3 Creteria (2 Text & 1 Number column)

Thread Information

Users Browsing this Thread

Similar Threads

Index Match with Multiple Criteria (exact for text and greater than value for number)

Formula Help Needed - Sumifs or Index Match - Text and number formats concatenat

[SOLVED] Index match match with the column number determined by two rows???

[SOLVED] Converting Hlookup to Index Match with variable column index number

Index match using multiple creteria

[SOLVED] Incorrect column number returned in INDEX, MATCH for nth instance

Using a column number found using MATCH to act as the INDEX column field

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions