Look-up with Multiple Criterias

Dear Excel Forum Users,

I am currently faced with the option to try do a look-up with multiple criterias. Below is the sample for index match lookup that I was working on.

=INDEX(Sheet1!D1:D10,MATCH(1,INDEX((Sheet1!B1:B10=B50)*(Sheet1!C1:C10=C50),0,1),0))

While the look-up works if the value of cell B50 and cell C50 corresponds with the column B1:B10 and C1:C10 respectively, I am hoping to add an element to "ignore" the criteria if it is blank.
In other words, if the cells B1:B10 is completely empty, the look-up should continue to work - however it will just be based on the other set of criteria in cell C50, which will do the look-up from cells C1:C10.

Currently, if I were to leave B1:B10 empty, the look-up will not work as it is unable to match any corresponding values based on cell B50.

Happy to listen to other alternatives. Many thanks in advance.

Jensen

Could you post copy of your spreadsheet?

Dear Dewilk,

Attached is the sample file.
The error is in cell K3. Assuming that all Grade 38 employee irrespective of their department will get £30 for car cost.
Instead of keying in all permutations of departments in the cost matrix, I wish to leave it as blanks - so the cost will be applied to all employees as long as their criteria for grade (38)is met.

Jensen

Thanks for you reply Stefano!

If I understand you correctly, you are suggesting to hard-code the cost to display £30 if the grade is 38.
Unfortunately I am trying to avoid that as the cost matrix may change very frequently so may not be the best option to lock in to a number or to a cell. Please pardon me for not being clear earlier!

What's the criteria set in cell B50 and C50 are going to be?

Dear Dewilk,

Dells B50 and C50 will be equivalent to cell H3 and I3 in the sample file.

Thanks!