Is there another way to write INDEX(INDIRECT(SUBSTITUTE()),MATCH(OFFSET(SUBSTITUTE()))?

[keby1nko]

Can Anyone Help on this formula?

Cell D9=INDEX(INDIRECT(SUBSTITUTE(B9&"Size"," ","")),MATCH(C9,OFFSET(INDIRECT(substitute(B9&"Size"," ",""),,,,1)),2)))

Excel display You have enter too many argument or inputs. Is there a simpler way to write this formula?

Thank you

EXCEL Attachment
pick steel bvj.xlsx

[Vikas_Gautam]

Here is my way..

Please Login or Register  to view this content.

check the attachment..

[keby1nko]

Hi vikas, first of all thanks for the help and reply, but i dun seem to understand the use of column(A1) in the formula? can you enlighten me on that? Thank you once again.

[Vikas_Gautam]

Yes, of course, I will
Actually there are two columns in the Calculated index ( USE FORMULA AUDITING TO UNDERSTAND MORE )
So when I drag the formula horizontally, the reference Column(A$1) changes to Column(B$1) to result the respective element on second column..


I hope It helps..

Regards,

[keby1nko]

Hmm, let me elaborate my question. Is the reference cell column(A$1) refer to the same meaning as vlookup(C9,B13:D14,2,FALSE)?

And one more question if you don't mind. The example that i provide in this thread consist of a data of 2x2 table but what if the number are uneven?
For instance if the category1 (CSplate contain 10x2 data) and category2 (SS304 plate contain 20x2 data) can i follow back the same formula?