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


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 :)


This question generated 19 answers. To proceed to the answers, click here.

This thread is current as of September 04, 2014.


For more resources for Microsoft Excel