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

Retrieve Data if both condition match

S

sambit

Member
Sir,
please help to retrive qty. from data sheet if both coloum of report sheet match with coloum of data sheet.

For your reference plz find attachment.
 

Attachments

Hi,

Here's one option: On Report Sheet cell D3 and copy down:

=SUMIFS(Data!$D$5:$D$16,Data!$B$5:$B$16,Report!B3,Data!$C$5:$C$16,Report!C3)

Another option:

=IFERROR(INDEX(Data!$D$5:$D$16,MATCH(Report!B3&Report!C3,Data!$B$5:$B$16&Data!$C$5:$C$16,0)),"")

But this one is array formula so must go with Ctrl+Shift+Enter.

Regards,
 
Hi Somendra

Sorry for pitching in on this thread. I have a query here. When I am trying to add the array formula with CTL+Shift+Enter. It give me the result on a cell, but when I am dragging the cell in the column then the reference value of the data sheet is getting changed say from default in the formular $D5:$D16 get changed for every cell. For example the next cell B4 the data sheet value gets changed to $D6:$D17. It keep on increasing for every cell. Please let me know how is it changing the deference cell value of Data sheet even after using "$" sign in the formula.

Is there any different way to drag the array formula.

Regards,
JD
 
Hi JD,

Using cell ref like this $D5:$D16 has column absolute but relative row ref. So if you use this in B2 and copy the formula in B3 the row ref. will increase by 1. That is the reason I locked both row & column ref. in my original formula.

Regards,
 
Hi Somendra

Thanks for the explanation. I think I was doing something wrong. I am aware of using "$" sign to lock the column and row reference value. I had manually applied the formula in Report sheet in B3 cell and dragged it down to B16, I was not getting the result, but I am getting it now. I was little surprise with the formula in the blank cell which was changing the locked value ($) by one for every next cell in the column. That is why thought to ask you the reason.

I am getting the result now after dragging the formula from B3 to B16. I was wondering if there is a different way to drag the array formula which is applied with CTL+Shift+Enter key.

Thanks for your clarification.

Regards,
JD
 
@Jagdev Singh

Dragging formula is copying the formula keeping the structure same as in the first cell or keeping same as from with it is dragged.

Ctrl+Shift+Enter has nothing to do with dragging. It is a special key stroke set, to tell Excel that you are demanding an array calculation in the formula entered, where Excel is not expecting an array calculation.

In the above formula Data!$B$5:$B$16&Data!$C$5:$C$16 this part as lookup array inside MATCH function is an array calculation. MATCH require an array or range (which is also an array) but not a calculation which will generate an array. So we need to tell Excel that you are asking him to do an array calculation and there by use the result of that calculation in MATCH function. So, Ctrl+Shift+Enter.

If you select the lookup array in MATCH function and press F9, that will hard code the values as array in MATCH function than you can execute the whole formula with just ENTER, and drag it down, will generate the right result.

Try experimenting with this.

Regards,
 
Hi Somendra

Please find the attached file and let me know what I am doing wrong after adding the formula manually in "Report tab" in "D4" cell. The next step I did is dragged the formula to rest of the cells in the column D.

Regards,
JD
 

Attachments

Hi Somendra

I got the issue and fix it. I was locking only the column and not the cell. What I was doing is "$D5:$D16" instead of using the exact form i.e. "$D$5:$D$16". This was the miss which was causing the issue.

Thanks for your support and help.

Regards,
JD
 
You must log in or register to reply here.
Back
Top