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

Indexmatch very simple formula needed help plz(screenshot attached)

N

nicholes

New Member
i want to fill some data to be filled automatically but i am not getting the formula. here is what i want...


when i write customaer name in sold field (b3) all the entry should automatically be filled (g3 h3 i3 j3)in his creadit account from (a3 c3 d3 e3).


somehow i managed to get it work(for one field only) but there is a problem in my formula

here is my formula =INDEX(A3,MATCH($L$1,B3,0),0)

Note:- l is where i wrote customer name


in leaves blank spaces when other customer name comes


11vgv1i.jpg



give me one example i would deal with others myself.

Thanks in advanced.
 
Hi nicholes,


Enter this formula in G3 as array formula (Ctrl+Shift+Enter) and drag across to J13. Remember to Change "A" to Whatever you want or just link it to a cell!


Code: 
=IFERROR(OFFSET($B$1,SMALL(IF($B$3:$B$11="A",ROW($B$3:$B$11)),ROW(D11))-1,COLUMN(D11)-1),"..")


Regards,

Faseeh
 
Hi, faseeh your idea did not for me


also i can not drag it but only vertical or horizontal only

BTW what b1 and d11 is doing there i dont get it


finally i want some detail how to grag it to j13???


@vijay i am just looking at your idea


Thanks for all your help
 
Hi nicholes,


See this file:


http://dl.dropbox.com/u/60644346/test_myworkout.xlsx


Regards,

Faseeh
 
oh faseeh! you made my day THANKS. it is such a relief to get replay like you does


THANKS again...

Thanks to all
 
hy faseeh in your file the date is not visible. instead of date there is customer name!! can you plz take look???
 
Hi Faseeh,


I had a look at your spreadsheet and your formulae is awesome. Will it be possible for you to break down the formula ?


Thanks for your time.


Z.
 
Hi, faseeh


i was just looking at the formula by selecting random cell and click in formula bar and when i click outside the cell ,the cell goes blank! it is happening every time in each cell when i click in formula bar.


what actually i have noticed, whenever i click in formula bar and click outside, the brackets {} removes automatically. and nothing happened when i insert them manually i had to "UNDO" every time.


is there any idea to lock formula? or any sugestion?
 
I believe that is because of the array formula.


To fix that please enter "ctrl+shift+enter" combination in cell, whenever you try to change formula.


Cheers.
 
Hi nicholes,


mrzoogle is right! Array formula need to be entered with ctrl+shift+enter so when you simply press enter they will give you maliciousness results.


=IFERROR(OFFSET($B$1,SMALL(IF($B$3:$B$11="A",ROW($B$3:$B$11)),ROW(D11))-1,COLUMN(D11)-1),"..")


@mrzoogle! The formula starts by matching if $B$3:$B$11="A" or not, if yes then it will return the corresponding row numbers. With ROW(D11))-1, I set the small formula for 1, 2, 3 smallest values, Thus the values retrieved from If() will have to pass test for being first smallest, second smallest and so on. The offset() function uses these values to locate the row in which corresponding match is present. COLUMN(D11)-1 is used to define the column offset. That is it. BTW this is part of a larger formula by Luke M. so you can see Formula Forensics No. 03 for detailed explanation.


Regards,

Faseeh
 
Hi Faseeh,


Thanks for the reply. There are few questions though. I tried breaking down the formula apart to see different results on how they work.

I am little confuse why ROW(D11)-1.

For example in your previous spreadsheet cell "J13" contains the formuale mapping to ROW(11)-1. Shouldn't it be ROW(13)-1 ?


Also I tried just this part IF($B$3:$B$11="A",ROW($B$3:$B$11)) to see the return value and it returned FALSE.

Will it be possible for you to give me feedback on this.


Thanks for your time.


Cheers,


Z.
 
is it possible to use different formula for each column? i dont want to use array formula,

also it hard to understand how it works.
 
=IF(B3:B13=$L$1,A3:A13,"")


i have got this simple formula but it leaves spaces when customer name does not comes

(L1 is customer name's cell)
 
Hi mrzoogle,


First your question:


Code: 
Why Shouldn't it be ROW(13)-1 ? Ans: You are mixing up two things. One thing is that you missed considering on closing bracket so that statement  in your post should be Row(D11))-1. Now the first closing bracket refers to Row(D11) that is because we had two extra row on top of the table so we need to account for that and we need in D13, the 11th smallest value of the array and not the 13th value. The 13th value might need to be looked at in J15 perhaps.  


The second bracket that you missed is the bracket that separates and adjust small() value for the offset function that had to see one row above to the actual small value. That is simply to satisfy the referencing criteria. Try without this -1 and see what happens. 


Regarding your second question If() statement will not give just FALSE, it will give you an array of Row numbers plus false if criteria is not met like this: [code]{3;FALSE;5;6;7;FALSE;FALSE;10;FALSE}
.....so this is the way thing goes.


@nicholes


Here are some possible correction with =IF(B3:B13=$L$1,A3:A13,"")[/code]


1. In your formula you are referring to L3 that is wrong cell, you need to refer to J3 instead.


2. When you reach the $A$3:$A$13, the formula is not considering it as an array, if you define it as an array like --($A$3:$A$13), even then it is returning just one A3 value in all cells. so this is problematic. I hope it is clear now.


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top