Need a help for HLOOKUP(CONCATENATE formula

[vijubhau]

Respected Forum..
I am getting problem for HLOOKUP(CONCATENATE formula for two sheets attached herewith in excel.
In the Marksheet sheet I entered the marks of students and in another Result sheet I need the Result as per the Roll Number which is common in both sheet.

[protonLeah]

Named Ranges:
FullName =Marksheet!$B$2:$B$21
Mother =Marksheet!$C$2:$C$21
Seat =Marksheet!$A$2:$A$21
Subject.Mark =Marksheet!$D$2:$Q$21

formula in Result!G3:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

your sheets have too many useless merged cells and blank spaces before and after names, etc.

[vijubhau]

Thanks for the reply Sir... After Named Ranges I run this formula in the Sheet... It is also working good condition.. But when I drag the formula from G3 Cell to N3 Cell, its only work upto G3 & H3..
What am I doing wrong?

[Glenn Kennedy]

Since the same candidates are present in BOTH sheets, in the SAME order, you can use this in G3, copied across and down:

=SUMPRODUCT(--(Marksheet!$D2:$P2=G$2),Marksheet!$E2:$Q2)