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

How to capture data from multiple columns

K

Kumar@raja

Member
Dear Friends,

Could you please help me out how can capture the data when the same in multiple columns in case if V- lookup is not effective in order to the get the desired results ? In this regard attached herewith a sample workbook and need to capture the data from Sheet2 to sheet1 with respective employee code located at B1 ( Sheet1) followed by respective month located at B12. (Sheet1). Hence, kindly help me out and oblige.

Anticipating for your valued support.

Best Regards,
Kumar
 

Attachments

  • Sheet - 2013-14.xlsx
    46.4 KB · Views: 13
Dear Mr. Mishra,

Thanks a lot it is awesome.If you can explain bit a brief about the same would be greatly appreciated.

is it mandatory to use "{ }" brackets if using Index and Match formula jointly, here bit confusing about the formula.

However, thanks a lot for the valued help.

Best Regards,
Kumar
 
Hi Kumar,

To get the sum of TDS for each month, use the following formula.

{=SUMIF(Sheet2!$A$2:$A$1002,B12,Sheet2!$C$2:$C$1002)}
 
@Kumar@raja

The function uses INDEX & MATCH combination. I had assumed that for each employee No. there will be unique entry for each month & year. If there would have been a multiple entries than formula should have been different.

So formula is looking in column say for e.g. TDS, now to extract value It uses MATCH function, it will match (Employee No. + Month Year value) in Column of month Year+ column of Emp no. and return ordinal position which is used for row number argument of INDEX function. Than INDEX function will return the value.

Here to create unique value & (concatenating operator is used).

Now to your second question, on {} these curly brackets are not manually entered. They are inserted by Excel when you execute any array formula by Ctrl+Shift+Enter.

In INDEX+Match formula, you have to enter two array.
1. For INDEX array.
2. Lookup array in MATCH.

This combination don't require Ctrl+Shift+Enter, and can be directly Entered, only if the array arrguments in both the formulas are Range or an array entered directly as a constant (array constant).

But the formula that is in the file, is doing an array operation in LOOKUP array argument of MATCH. Now if you enter this formula directly by pressing Enter, Excel will treat this as an error because it is expecting an array not an array operation. (An array would have been a range like A2:A1000 or directly an array like {100;200;300;400;500}). So you have to tell Excel that you are doing an array operation and treat the result of this array operation as a lookup array, so Ctrl+Shift+Enter become a MUST. That's why you see {} around the formula.

Hope this clarify your doubt, if not, than write back.

Regards,
 
Hi Somendra,

Pardon, I could not convey my thanks to you, as I am away from system all these days. Thank you very much for taking time for explaining in detail. I have two quick questions; hence kindly let me know at your convenience.

1) Why this formula is not coping to below rows to apply the same ,which we do in normal course for all the functions. Here, I am trying to copy the formula from C12 to C23 but it is not allowing and only coping to only one row. In this case, if data is in sizeable amount may be takes considerable time isn’t?

2) Ctrl+Shift+Enter = what are the advantages for the same?

I am completely unaware about the same. I came to know only from you.Therefore, kindly guide me about the same at your convenience.

Once again thanks for your kind and Valued support.

@ Srinidhi,

Thank you very much for the formula.

Regards,
Kumar
 
Need help. How to return names mentioned in sheet 1 after matching DOB mentioned in sheet 2. Enclosed is the file.
 

Attachments

  • Name DOB.xlsx
    672.9 KB · Views: 3
Hi Ravi,

Try to post your problem as a new thread, saying that let us take the first date 13-Oct-95 there are 28 names on sheet1 with this DOB, which name you want back as a result?

Regards,
 
Thank you, next time I'll ensure to post as a new thread. I need help in fetching all names corresponding to a matching DOB. I would want to do this for all 70 DOB in sheet 2.

Regards,
Ravi
 
See the file. I had deleted some data as file was growing bigger than this forum can upload. You can also achieve this with pivot table also.

Regards,
 

Attachments

  • Name DOB.xlsx
    693.2 KB · Views: 4
You must log in or register to reply here.
Back
Top