Formula to calculate value after doing a data match

[Excel Dumbo]

Hello Dear Excel Friends,

Could you please refer the attached work sheet and advise a formula to calculate the values once the criteria is met.

Thanks
Excel Dumbo

[benishiryo]

hi there. try this for your Table1 in D4:
=$B4*INDEX($L$4:$O$5,MATCH($B$1,$K$4:$K$5,0),MATCH(D$1,$L$3:$O$3,0))

copy across the table. please note that your description in the Look Up Table has to be the same. M3 has an extra space.

for Table 2, adjust a little:
=$B12*INDEX($L$4:$O$5,MATCH($B$9,$K$4:$K$5,0),MATCH(D$1,$L$3:$O$3,0))

if you have many tables like this & too have too much trouble to adjust the formulas, let me know so i can try to do a universal one.

[Excel Dumbo]

Thanks a lot Benishriyo. This will definitely do the job. Works like magic.

There are indeed many tables, as this is just a sample file I have posted. I would be very much interested to know a universal formula.

Thanks heaps

[benishiryo]

you're very welcome. here's 2 other solutions, provided the layout is always standardised (same number of rows & columns in between each tables)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Excel Dumbo]

This is simply Superb. U made my day Benishriyo. This is absolutely amazing work. It works! Thanks mate. I will mark this thread as solved. If possible could you please help me with my other request i posted a few seconds back "A better pivot table"

[VKS]

Hi, I see that you already have an answer but i worked on a file which you may want to see

[Excel Dumbo]

Thank you VKS, defintely does the job

[Excel Dumbo]

Big stars to u too vks