IndexMatch + If as criteria?

[sh1ne]

Hello all,

I'm struggling with understanding and adding if as criteria to IndexMatch formula. My setup looks like this:
tab data:

tab price:

my formula in column Z:

=INDEX(PRICE!$A$2:$E$3241;MATCH(DATA!Y2;PRICE!$E$2:$E$3241;0);2)

lets use row2 values as example in below sentence:
My formula return value from B2(PRICE) if value in E2(PRICE) matches with Y2(DATA). I would like to include criteria that checks if X2(DATA) is higher than A2, if yes, then proceed to A3 until requirement will not pass test.
Multiple criterias for Match looks like (array=value), how could I add If function there?


It's possible to do within formula or I would need to use VBA?

Kind regards

 

[jasonb75]

If the blanked out prices in column B decrease or increase going down the table then you should be able to use MINIFS or MAXIFS (without any of the other functions).

If the values are in no order, or if you have an older version of excel that doesn't have these functions then you will need an array formula.

Either way, INDEX and MATCH is not the way to go here.

 

[Peter_SSs]

It's possible to do within formula or I would need to use VBA?

One thing to help best answer that is to know what Excel version you are using. Therefore, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also help if you gave us some dummy data and expected results that can both see and copy. Have a look at XL2BB

 

[sh1ne]

If the blanked out prices in column B decrease or increase going down the table then you should be able to use MINIFS or MAXIFS (without any of the other functions).

If the values are in no order, or if you have an older version of excel that doesn't have these functions then you will need an array formula.

Either way, INDEX and MATCH is not the way to go here.

It decreases but still I need column D as a criteria, I know that without this column I could use even LOOKUP?

One thing to help best answer that is to know what Excel version you are using. Therefore, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also help if you gave us some dummy data and expected results that can both see and copy. Have a look at XL2BB

I've added info about my version. Thanks

 

[Peter_SSs]

I've added info about my version.

Thanks for that.

.. but not this?

It would also help if you gave us some dummy data and expected results that can both see and copy. Have a look at XL2BB

We can't do any testing on a picture.

A small set of dummy data from each sheet, with the expected results entered manually would be a great help.

 

[jasonb75]

still I need column D as a criteria

Column D, or E (as in original picture)?

With your version of excel and the data pattern shown, I would suggest something like this.

=AGGREGATE(14,6,Price!$B$2:$B$100/(Price!$E$2:$E$100=Y2)/(Price!$A$2:$A$100>X2),1)

 

[sh1ne]

Column D, or E (as in original picture)?

With your version of excel and the data pattern shown, I would suggest something like this.

=AGGREGATE(14,6,Price!$B$2:$B$100/(Price!$E$2:$E$100=Y2)/(Price!$A$2:$A$100>X2),1)

sorry i meant column E as in original picture, then it should match Weight value(column X) with range from column A.

I'll update reply once I get into excel and check your formula, thanks for feedback!

 

[sh1ne]

Column D, or E (as in original picture)?

With your version of excel and the data pattern shown, I would suggest something like this.

=AGGREGATE(14,6,Price!$B$2:$B$100/(Price!$E$2:$E$100=Y2)/(Price!$A$2:$A$100>X2),1)

thanks a lot! Formula is returning value based on criteria but I have one small problem to be solved(I'm thankful to current solution).

Based on this example:

If weight dont surpass value from column A - it should return value from upper cell. To better example: if weight is 600(dont surpass 601) then it should be 9.05, not 8.49. If weight is 135 it takes 13.59 value not 10.20.

Hopefully it's understable, anyway your's formula helped me a lot already.


sorry for double post

 

[jasonb75]

Sorry, that was my mistake. You asked correctly in your previous post but I had the formula logic reversed.

=AGGREGATE(15,6,Price!$B$2:$B$100/(Price!$E$2:$E$100=Y2)/(Price!$A$2:$A$100<=X2),1)