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

[Ferlucia]

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

 

[Aladin Akyurek]

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>

What is the error that occurred?

By the way, the formula should have been:

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

 

[Aladin Akyurek]

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>

Here is a link to the workbook that implements the set up.
https://dl.dropboxusercontent.com/u/65698317/Ferlucia%20multiconditional%20look%20up%20query.xlsx

 

[Ferlucia]

I copied it from your sheet, pressed crtl+shift+enter but I still got an #N/B error.
I realy don't know what I'm doing wrong, but when I press evaluate it underlines this:
=INDEX(Blad2!$C$5:$D$14; MATCH($F31;IF(Blad2!$B$5:$B$14=$J31;Blad2!$A$5:$A$14);0); IF($L31>50;2;1))

 

[Aladin Akyurek]

I copied it from your sheet, pressed crtl+shift+enter but I still got an #N/B error.
I realy don't know what I'm doing wrong, but when I press evaluate it underlines this:
=INDEX(Blad2!$C$5:$D$14; MATCH($F31;IF(Blad2!$B$5:$B$14=$J31;Blad2!$A$5:$A$14);0); IF($L31>50;2;1))

When you open the workbook I posted, do you see any error?

 

[steve the fish]

Invoking SumProduct for a retrieval task is a risky idea...

Hi Aladin

Why is sumproduct risky here?

Thanks Steve

 

[Aladin Akyurek]

Hi Aladin

Why is sumproduct risky here?

Thanks Steve

In case of 2 or more records that satisfy the conditions will SumProduct sum the prices instead of retrieve a price.

 

[Ferlucia]

It works.
I don't realy know why but its just like Steve's formula, when I entered it a second time, it suddenly worked.

Thanks to both of you

 

[Aladin Akyurek]

It works.
I don't realy know why but its just like Steve's formula, when I entered it a second time, it suddenly worked.

Thanks to both of you

What did suddenly work?

 

[Ferlucia]

The formula you gave me
=INDEX(Blad2!$C$5:$D$14; MATCH($F31;IF(Blad2!$B$5:$B$14=$J31;Blad2!$A$5:$A$14);0); IF($L31>50;2;1))

When I tried it yesterday, I constantly got errors. But when I tried it again today it suddenly worked for some reason.

 

[Aladin Akyurek]

The formula you gave me
=INDEX(Blad2!$C$5:$D$14; MATCH($F31;IF(Blad2!$B$5:$B$14=$J31;Blad2!$A$5:$A$14);0); IF($L31>50;2;1))

When I tried it yesterday, I constantly got errors. But when I tried it again today it suddenly worked for some reason.

It's great that it worked as it's supposed to do.