Lookup value from another sheet using Sumproduct function [SOLVED]

[Rajender]

Hi All,


Please find the link for the same.


https://skydrive.live.com/#cid=393CD98CEE2A349F&id=393CD98CEE2A349F!105


I need to get the name of trainings(showing in sheet "Certification Month Wise" from range H3:T4.

the result I need to get in sheet name "Final Sheet".

The unique value by which we need to lookup is " Signum ID" range "C3:C4".


I need all the training names in front of all "Signum ID's" corresponding to "months mentioned in Final Sheet from range H1:N2"


I tried the formula =SUMPRODUCT(($C4='Certification Month Wise'!$C$3:$C$281)*('Final Sheet'!$H$1:$N$2='Certification Month Wise'!$H$3:$T$281),'Certification Month Wise'!$H$3:$T$4)" , but not successful showing NA error.


Regards,

Rajender

 

[Debraj]

Hi Rajender,


Welcome to the forum, we are glad you are here..


I Dont know.. if SumProduct can return TEXT or not.. but try this..


In H3 Use CSE Formula as

=IFERROR(INDEX('Certification Month Wise'!$H$3:$T$4,,MATCH(TEXT('Final Sheet'!H$1,"mmm-yy"),TEXT(OFFSET('Certification Month Wise'!$C3,MATCH('Final Sheet'!$C3,'Certification Month Wise'!$C$3:$C$281,0)-1,5,,13),"mmm-yy"),0)),"")

Confirm the formula by pressing Ctrl + Shift + Enter, not just Enter.


Now drag toward.. down or right..


Regards,

Deb

 

[NARAYANK991]

Hi Rajender ,


Can you verify whether this file gives the correct results ?


http://sdrv.ms/1bLHsGR


Narayan

 

[Rajender]

Hi Narayan & Deb,


Thanks a lot for thr solutions.

Both the solutions working fine.


Regards,

Rajender