Index and match, snag?

[buzzing]

Hi,
Iam using Index and Match in my formula for column I, to Match a value in column J and index a value in I. According to the help file the following types of match can be done:
If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.


If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

I need to use match type 1, but my array values are in random order.

Is it possible to make match type 1, work with "LOOKUP array can be in any order".
If not which other function would work?
Any ideas?

 

[buzzing]

Hi there,

I am using the following formula dragged down column I (See snapshot above.) At I20 is reads:

=IF(ROW()-ROW($I$12)<=$I$12,$I$12,IF(OR(G19=0,G19<I$12),INDEX(I$13:I19,MATCH(J20,J$13:J19,1)),IF(G19>=I$12,I19+1)))

Can anyone explain why it returns 4 at I20, if I am reading it right it should index and match J20 and return 5 not 4 at I20.

Any ideas?


Edited by Von Pookie to show entire formula

 

[Aladin Akyurek]

Hi there,

I am using the following formula dragged down column I (See snapshot above.) At I20 is reads:

[=IF(ROW()-ROW($I$12)<=$I$12,$I$12,IF(OR(G19=0,G19<I$12),INDEX(I$13:I19,MATCH(J20,J$13:J19,1)),IF(G19>=I$12,I19+1)))]

Can anyone explain why it returns 4 at I20, if I am reading it right it should index and match J20 and return 5 not 4 at I20.

Any ideas?

Why don't you post the formulas (in plain text) that you have in

G13

I13

and

J12

along with the expected values in I?

 

[buzzing]

Hi there,
In G13 IF(H13<1.1,1,0)
In G14 IF(H14<1.1,SUM(G13+1),0) ( this is then dragged down the column)
In I13 The same as I20 (formula dragged down the column)
In J12 (I12*K12)+L12
In J13 =J12
In J14 IF(L13<1,J13+K13,IF(J13-L13<J$12,J$12,J13-L13)) (this is then dragged down the rest of column)
Ps see edited version above, of I formula.

The expected values in I are as follows:
I20 5
I21 6
I22 7

Hope this helps?
buzzing

 

[Aladin Akyurek]

Hi there,
In G13 IF(H13<1.1,1,0)
In G14 IF(H14<1.1,SUM(G13+1),0) ( this is then dragged down the column)
In I13 The same as I20 (formula dragged down the column)
In J12 (I12*K12)+L12
In J13 =J12
In J14 IF(L13<1,J13+K13,IF(J13-L13<J$12,J$12,J13-L13)) (this is then dragged down the rest of column)
Ps see edited version above, of I formula.

Hope this helps?
buzzing

Type the formula that you have in I13 as is (put spaces around "<" when posting).

Type what figures/numbers do you expect to see in I?

I presume you have in K and L just numbers, no formulas.

 

[buzzing]

Hi there,
In G13 IF(H13<1.1,1,0)
In G14 IF(H14<1.1,SUM(G13+1),0) ( this is then dragged down the column)
In I13 The same as I20 (formula dragged down the column)
In J12 (I12*K12)+L12
In J13 =J12
In J14 IF(L13<1,J13+K13,IF(J13-L13<J$12,J$12,J13-L13)) (this is then dragged down the rest of column)
Ps see edited version above, of I formula.

Hope this helps?
buzzing

Type the formula that you have in I13 as is (put spaces around "<" when posting).

Type what figures/numbers do you expect to see in I?

I presume you have in K and L just numbers, no formulas.

Hi there,
I13 is IF(ROW()-ROW($I$12) < =$I$12,$I$12,IF(OR(G12=0,G12 < I$12),INDEX(I12:I$13,MATCH(J13,J12:J$13,1)),IF(G12 > =I$12,I12+1))) (dragged down the column)

at I20 5
at I21 6
at I 22 7
Column K is, at K13 =J13/I13 (dragged down the column)
Column L is, at L13 =K13*H13-K13 (dragged down the column)
Thank's for your input, hope this helps!
buzzing

 

[buzzing]

Hi saurabh,
quote, =INDEX(I$2:I$6,MATCH(A2,J$2:J$6,0),1)
Thanks for offering the above, but can you explain what the ,1 on the end is about?
buzzing

 

[fairwinds]

INDEX(array,row_num,column_num)

It should be the column number but as you have only one column here you can take it away.

 

[buzzing]

Hi there,
quote, =INDEX(B2:B10,MATCH(LARGE(A2:A10,COUNTIF(A2:A10,">="&C3)+1),A2:A10,0))
Thanks to fairwinds for offering the above.
I have to say i dont understand the ">="&c3 bit though, can anyone enlighten me?
buzzing

 

[fairwinds]

From help file:
Syntax

COUNTIF(range,criteria)

Range is the range of cells from which you want to count cells.

Criteria is the criteria in the form of a number, expression, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples"."


And from the above... if you want to fetch the criteria 32 from cell A1 you must do

=COUNTIF(B1:B10,">"&A1)

 

[buzzing]

Thanks for the example, I get where your at with the formula now, i'll give it a try.
Thanks for your patience!
buzzing.
Learning all the time!