Combining IF/ INDEX/ MATCH with multiple criteria over multiple sheets

Ferlucia

New Member
Joined
Jul 3, 2014
Messages
43
Hello everyone,

I know this is a question that's been asked a lot (I know cause I read them all).
But for some reason it doesn't work for me, so I wonder what I'm doing wrong.

What this formula should do:
It has to search the right price of an article based om 2 criteria: the name (column A) and the quality (column B)
IF the amount is lower than 50, it needs to take the price out of column C, and IF it is above 50 then it has to take the price out of column D.

The sheet with all the prices per article is sheet 2 (Blad2)
The sheet where I type the name (F31), the quality (J31) and the amount (L31) is sheet 1 (Hout-idee NL).

This is the formula I have:
=IF(L31<=50;INDEX(Blad2!$C$5:$C$14;MATCH(F31&J31;Blad2!$A$5:$A$14&Blad2!$B$5:$B$14;0));INDEX(Blad2!$D$5:$D$14;MATCH(F31&J31;Blad2!$A$5:$A$14&Blad2!B$5:B$14;0)))

But I constantly get errors, no matter how i adjust it...

The answer is probebly simple and obvious, but I don't see it.

Hope you can help me :)
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

azumi

Well-known Member
Joined
Jun 4, 2013
Messages
555
Maybe you need to lock your criteria on cell J31

IF(L31<=50

when you copied your formula down or cross, your condition will go away
 

Ferlucia

New Member
Joined
Jul 3, 2014
Messages
43
I'm afraid I don't understand.

I haven't copied this formula jet
This is the original witch I'm trying to get to work first, before I copy it...

Or did you mean something else?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,402
Office Version
  1. 365
Platform
  1. Windows
Try this (you may need to extend the amount of rows):

=IF($L$31<50,SUMPRODUCT(--(Blad2!$A$2:$A$51=$F$31),--(Blad2!$B$2:$B$51=$J$31),Blad2!$C$2:$C$51),SUMPRODUCT(--(Blad2!$A$2:$A$51=$F$31),--(Blad2!$B$2:$B$51=$J$31),Blad2!$D$2:$D$51))
 

Ferlucia

New Member
Joined
Jul 3, 2014
Messages
43

ADVERTISEMENT

I tried and I still get an error.
When I pressed evaluate, I got many rows of numbers and true, not true values.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,402
Office Version
  1. 365
Platform
  1. Windows
Many rows?? You should have only one formula as you are only looking for one price
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

Hout-idee NL

F31 houses an article name.
J31 houses a quality qualifying value.
L31 houses an amount.

Control+shift+enter, not just enter:
Rich (BB code):
=INDEX(Blad2!$C$5:$D$14;
  MATCH($F31;IF(Blad2!$B$5:$B$14=$J31;Blad2!$B$5:$B$14);0);
  IF($L31>50;2;1))
 

Ferlucia

New Member
Joined
Jul 3, 2014
Messages
43
For some reason, It suddenly works!
(Maybe I pressed a wrong button somewhere the first time?)

Thank you so much :)
 

Ferlucia

New Member
Joined
Jul 3, 2014
Messages
43
That might be true,
So I tried your formula as well, but it gave an error :s

When I pressed evaluate it underlined this part:

=INDEX(Blad2!$C$5:$D$14; MATCH($F31;IF(Blad2!$B$5:$B$14=$J31;Blad2!$B$5:$B$14);0); IF($L31>50;2;1))</pre>
 

Watch MrExcel Video

Forum statistics

Threads
1,130,102
Messages
5,640,112
Members
417,126
Latest member
Jeffman52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top