• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vlookup formula error?

S

sachar

Member
Dear all,
With reference to the attachment having some problem in Vlookup formula, Please help me to find error?
 

Attachments

  • VLOOKUP FORMULA.xlsx
    21.7 KB · Views: 0
you can use thisformula =INDEX($B$4:$G$8,MATCH(B$30,$A$4:$A$8,0),MATCH($A31,$B$3:$G$3,0))

in general index match is good combination when looking up in two directions
 
Hi,
Using sumproduct, Please find attached file.
=SUMPRODUCT(($A$4:$A$8=B$30)*($B$3:$G$3=$A31)*($B$4:$G$8))
 

Attachments

  • VLOOKUP FORMULA.xlsx
    21.8 KB · Views: 0
Dear Syed,

Your formula looks very simple, but, I could not get it, would you please explore its?

Thanking you.
 
hey sachar,

its very simple just understand the concept 1st try to understand the format of sumproduct how it is being used, then just focus on the 1st criteria that says $A$4:$A$8=$B30 it means it is checking/matching the date according to cell B30 ie equal to jan-12...and so on...

afterwards the second criteria $B$3:$G$3=A$31 matching the name with both the tables and then the third multiplying & adding up the whole array...I hope it helps....coz it also took a lot of time to understand sumproduct but it is very amazing formula...

4 more clarification u can write back

regards,
jaya
 
Ms Jaya,

Would you help to understand in arithmetic? When I illustrate the result is zero, sample file is attached.
 

Attachments

  • VLOOKUP FORMULA_with sumproduct.xlsx
    24.8 KB · Views: 0
sachar said:
Ms Jaya,

Would you help to understand in arithmetic? When I illustrate the result is zero, sample file is attached.
Click to expand...

Hi sachar,
I think what you are doing is wrong, SUMPRODUCT can not be illustrate like you did.

If you evaluate this formula:
=SUMPRODUCT(($A$4:$A$8=B$30)*($B$3:$G$3=$A31)*($B$4:$G$8))

you will see how this works with TRUE(s) / FALSE(s):

=SUMPRODUCT({TRUE;FALSE;FALSE;FALSE;FALSE}*({TRUE,FALSE,FALSE,FALSE,FALSE,FALSE})*{72,76,75,80,53,57;60,65,36,35,72,76;80,48,67,58,78,42;71,75,43,66,37,73;74,38,36,59,65,67})

Next:
=SUMPRODUCT({1,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0}*{72,76,75,80,53,57;60,65,36,35,72,76;80,48,67,58,78,42;71,75,43,66,37,73;74,38,36,59,65,67})

Finally:
=SUMPRODUCT({72,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0})

Hence it will return 72

Hope it helps.

Regards,

P.S:
Just noticed you are transposing your data, so this can be achieved with just:
=TRANSPOSE(B4:G8)
you have to select range B31:F36
now enter formula with Ctrl+Shift+Enter
 
jayalaxmi said:
hey sachar,

its very simple just understand the concept 1st try to understand the format of sumproduct how it is being used, then just focus on the 1st criteria that says $A$4:$A$8=$B30 it means it is checking/matching the date according to cell B30 ie equal to jan-12...and so on...

afterwards the second criteria $B$3:$G$3=A$31 matching the name with both the tables and then the third multiplying & adding up the whole array...I hope it helps....coz it also took a lot of time to understand sumproduct but it is very amazing formula...

4 more clarification u can write back

regards,
jaya
Click to expand...
Ms. jayalaxmi,

Now, i got it,Many... thanks.
 
Khalid NGO said:
Hi sachar,
I think what you are doing is wrong, SUMPRODUCT can not be illustrate like you did.

If you evaluate this formula:
=SUMPRODUCT(($A$4:$A$8=B$30)*($B$3:$G$3=$A31)*($B$4:$G$8))

you will see how this works with TRUE(s) / FALSE(s):

=SUMPRODUCT({TRUE;FALSE;FALSE;FALSE;FALSE}*({TRUE,FALSE,FALSE,FALSE,FALSE,FALSE})*{72,76,75,80,53,57;60,65,36,35,72,76;80,48,67,58,78,42;71,75,43,66,37,73;74,38,36,59,65,67})

Next:
=SUMPRODUCT({1,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0}*{72,76,75,80,53,57;60,65,36,35,72,76;80,48,67,58,78,42;71,75,43,66,37,73;74,38,36,59,65,67})

Finally:
=SUMPRODUCT({72,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0;0,0,0,0,0,0})

Hence it will return 72

Hope it helps.

Regards,

P.S:
Just noticed you are transposing your data, so this can be achieved with just:
=TRANSPOSE(B4:G8)
you have to select range B31:F36
now enter formula with Ctrl+Shift+Enter
Click to expand...
Mr. khalid,

Now, it is more clear with help of your's details.
Thanking you
 
You must log in or register to reply here.
Back
Top