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

Extracting Specific Data from a table [SOLVED]

MSO_user

New Member
Dear Friend

Hoping for your good health and safety.

Please review my excel sheet. I have done some data extraction from table using index, match and indirect formulae.

We choose Case (from 1 to 7) from list and the respective factors against the item is displaced.

However, I would request, to please guide me regrading following points:

1.Any other optimized way.
2.Any thing I am doing in the wrong way.

Reply would be appreciable. I am using MS office 2010.

Thanks
 

Attachments

p45cal

Well-Known Member
Try re-arranging your data into a simple 1 row per case table. Then you can use a plain index/match/match formula.
See Sheet1 (2) in the attached.

Sheet1 (3) is another version were I've made the table into a proper Excel table. This means (with the added Name Cases that you can add rows and columns to the table without the need to change the data validation in cell C20 or the formulae in cells C23:C27 because tables usually adjust their size to accommodate added rows and columns. I hope this works in Excel 2010!
 

Attachments

Last edited:

bosco_yip

Excel Ninja
The better option is to avoid using the Indirect function of which is a volatile function

Here is a Index+Match formula for your reference

In C23, formula copied down :

=IFERROR(INDEX($B$3:$D$15,MATCH(C$20,$A$2:$A$14,0),MATCH(D23,INDEX(B$2:D$14,MATCH(C$20,A$2:A$14,0),0),0)),0)

79657
 

MSO_user

New Member
Dear Friend

Please tell me, how to mark this thread as SOLVED....

I think I have got my answer....

Much thanks for your support in this regards....
 
Top