Vlookup a value with multiple criteria between two tabels

Hi

I am wanting to to do a VLOOKUP to show me a value for a item number. However I want it to only show a value if dates and additional criteria are met between two tables.

Please see the example in attached. Any assistance would be great.

I have attached comments in the excel file so it makes more sense what I am looking to acheive.

On Sheet Table 1, in cell C4, try this:

=INDEX('Table 2'!$C$5:$W$5,MATCH($A4,'Table 2'!$A$5:$A$25,0),MATCH(C$1&C$3,'Table 2'!$C$1:$W$1&'Table 2'!$C$3:$W$3,0))

The part that says "$A$5:$A$25" for $A$25, you would put in the last row of data.

You can then copy the formula across.

Hi

I tried you formula and it is showing "VALUE". I have tried something else to see if it helped and still having no luck. This is the formula used:

=VLOOKUP(A4,'Table 2'!$A$5:$W$100,MATCH(1,('Table 2'!$A$1:$W$1=C$1)*('Table 2'!$A$3:$W$3=C$3),0),0)

It shows the value but when I try it in the other cells fore example:

D4 , E4, F4 etc...

I just get #N/A.


Any ideas would be apricated at this point as I am totally out of ideas for now.

Yes, thanks John. Since I'm on MS365 I don't have to worry about array formulas so I forget sometimes when earlier version require entering them with Ctrl+Shift+Enter.