• 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.

Index Match Query

E

Ecel Dumbo

Member
HI Friends,

Could you please kindly refer the attached worksheet and help frame a formula ? I'm guessing index match is a good one to use. If there is anything easier, would be really great. I am using Excel 2003 . Thanks heaps​
 

Attachments

  • Consignment -Index Match.xls
    65.5 KB · Views: 15
I was able to follow the example formula you posted, underneath table, but couldn't understand how it fits with your description on bottom right. Here is the workbook populated with formulas to calculate as per lower example.

Note that all the errors are for cases where no values exist, thus creating a division by 0 problem. Can you show further what you would like?
 

Attachments

  • Consignment -Index Match.xls
    88.5 KB · Views: 12
Hi, Ecel Dumbo!
Arrived at the same formulas that Luke M and we both get different values against your manual calculations:
C8 179.2872 instead of yours 179.10
C9 167.213115 instead of 168.8
... and continues.
Have you manually checked the figures?
Regards!
PS: In fact I didn't see Luke M's post, otherwise I'd have never tried to build such monstrous formulas :(
 
Hi Luke , your formula works perfect. And you have now guided me to build formula for other cells (bottom right description in sheet). Thanks heaps.

one last question, how do i get rid of #DIV error, to make it look neat ? Please see attached
 

Attachments

  • Consignment -Index Match (Luke solving).xls
    68 KB · Views: 3
THanks Sir JB7, you are correct. I manually checked and there was mistake on some of those error. Please see attached file above. I had asked Luke to help me get rid of #DIV error
 
Hi, Ecel Dumbo!
Tried embedding the krakens within this structure?
Now:
=SUMPRODUCT(...)*30
Then:
=IFERROR(SUMPRODUCT(...)*30,0)
Still entered as array formulas.
Regards!
 
HI SirJB7 , Thanks you. I am using excel 2003. i tried and it is not working. I am getting #NAME error. Please see attached
 

Attachments

  • Consignment -Index Match (Sir JB solving).xls
    68 KB · Views: 2
Hi, Ecel Dumbo!
Well, in that case IFERROR wasn't discovered yet but... and blame Luke M for the new doubled kraken... you can try this:
=IF(ISERROR(SUMPRODUCT(...)*30),0,SUMPRODUCT(...)*30)
Regards!
 
Hi, Ecel Dumbo!
Glad you solved it. Thanks for your feedback and for your kind words too. I'll give Luke M his little part of the credit, but later.
And welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top