Multiple criteria Index Match

I am trying to return a value from a range based on multiple criteria. The formula used
=INDEX(Tbl_Pricing,MATCH(1,(Tbl_Pricing!$A:$A=C3)*(Tbl_Pricing!$B:$B=D3)*(Tbl_Pricing!$C:$C=E3)*(Tbl_Pricing!$D:$D<F3)*(Tbl_Pricing!$E:$E>F3),0),6). it gives me NA even when I narrow down the source table to one row to eliminate other price ranges. I am not sure where the problem is but I think it is in the (Tbl_Pricing!$D:$D<F3)*(Tbl_Pricing!$E:$E>F3) section of the formula. The "6" at the end of the formula is the number of the column where the price is.

Thanks

It will help a lot if you upload a small but representative subset of your workbook that illustrates the problem.
To attach a file click on “Go Advanced” then “Manage attachments”

Thank you. I have attached the file. In the second tab, I limited my table to one raw to see if the range is the issue. In the first tab, I did add a helper column to see
if digits would work better.

I have to admit I'm finding this a bit confusing with some of the column names in English and some in Arabic and figuring out where the formula in question is to be located.

I am assuming the formula we are testing goes on the "Auditors Palestine" worksheet in cell G2

Here's what I did to get the value 2.5 in G2 (which I think is what you expect - true?

a) You have a mixture of regular and Table style references - I changed them to Table style.
b) To simplify testing you created worksheet "Pricing (2)" with one row. I changed this to add a second row - I think this needs to be done to get the necessary arrays rather than single values that the formula requires.
c) The formula is an array formula and so needs to be entered with CTRL-SHIFT-ENTER

So in "Auditors Palestine"!G2 I have the following which yields 2.5

Formula:

Please Login or Register  to view this content.

Hopefully this is making progress towards what you are looking for.

Thank you, GeoffW283. It did work. The formula I was using is the same except for the regular style references. Was this the issue, you think? I did not know the references could cause such an issue. Thank you

G2 in your original was creating a !REF# error. This was due to the reference: Tbl_Pricing15!$B2
From the Name Manager, Tbl_Pricing15 refers to 'Pricing (2)'!$A$2:$I$3 so Tbl_Pricing15 is a range, so to say Tbl_Pricing15!$B2 is syntactically wrong - it would need to be INDEX(Tbl_Pricing15,1,2) which yields "4". Or alternatively you could use the worksheet reference'cell form: 'Pricing (2)'!$B2 which also yields 4 or, as I did, you could use a Table reference. All methods could be made to work.

Beyond the !Ref# error, you really want an array of values something like 'Pricing'!$B2:B53 for your full worksheet and similarly for the rest of the columns in the G2 formula. Using the Table style references gives you that array automatically (as long as the table has at least 2 rows). The !Num# errors you were seeing were, I think, due to providing a single number as the second parameter to "match" rather than the required array.

Hope this clarifies and does not do the opposite!

Thanks a lot, GeoffW283. I really appreciate the detailed response.

Monther