vlookup based on two different search factors - but how??

[exceltripping]

Hi all, I need to look up a value based on two search criteria:

1) the month and year must match but not the exact date

2) The reference number must match (The range being searched has other text too so search will based on 'contains text' sort of thing)

I can't quite figure out what formula I use and how do I implement it..any help would be much appreciated.

Hopefully the attachment explains a little better

[Haseeb Avarakkan]

Try this, C3 copy down & across.

=SUMPRODUCT(--(TEXT($A$14:$A$20,"mmm-yy")=TEXT(C$2,"mmm-yy")),--ISNUMBER(SEARCH(" "&$B3&" "," "&$B$14:$B$20&" ")),$D$14:$D$20)

[exceltripping]

Thanks Haseeb..Not sure I could ever do that...taking that to the next stage and me wanting to do this across two tabs...I have not been able to tweak the formula...I have attached a file again with the yellow bits highlighted and some commentary.

Any idea what's wrong with the formula?

Many thanks

[Haseeb Avarakkan]

remove the " "& par, so use like,

=SUMPRODUCT(--(TEXT('A&L'!$A$9:$A$391,"mmm-yy")=TEXT(O2,"mmm-yy")),--ISNUMBER(SEARCH(B3,'A&L'!B9:B391)),'A&L'!D9:D391)

Also all ranges MUST be in SAME SIZE, in your file it was, A9:A391, B9:B20, D9:D20. all ranges must be either 9:20 or 9:391

[exceltripping]

BINGO! many thanks. Rep added