array formula problem

[Guest]

i need to look up columns and match a number eg:
AH32 (25) match it in column AF(25) and return the result from column AG(4)
the result i get is 10 where it should be 4, i tried defferent ways but no
luck
i can not use VLOOKUP because i have the same numbers at times and need
multiple hits
AF AG AH result
32 blank 1 25 4
33 blank 2 14 2
34 14 2 11 3
35 blank 2 11 5
36 blank 3 11 6
37 11 3
38 blank 4
39 25 4
40 blank 5
41 blank 5
42 11 5
43 11 6
44 blank 6
and so on down to 300 rows
my array formula is :
{=INDEX(AG32:AG300,SMALL(IF(AF32:AF300=AH32,ROW(AF32:AF300)),ROW(1:1)))}
coppied down to 5 rows as 1 only need 5 results

can some one please help ?

greatly appreciated

bill gras

 

[Domenic]

Try...

AI32, copied down:

=INDEX($AG$32:$AG$44,SMALL(IF($AF$32:$AF$44=AH32,ROW($AG$32:$AG$44)-ROW($
AG$32)+1),COUNTIF($AH$32:AH32,AH32)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[RagDyer]

Try this array formula:

=INDEX($AG$32:$AG$300,SMALL(IF($AF$32:$AF$300=AH32,ROW($A$1:$A$269)),ROW(1:1
)))

Your Row() reference should indicate the *number* of rows in your array,
which in this case is 269 rows.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[RagDyer]

Since your copying down 5 rows for 5 returns of the same AH32 value, I
should really add the absolutes , just to be completely accurate.
Can also eliminate the column references in the array size.

=INDEX($AG$32:$AG$300,SMALL(IF($AF$32:$AF$300=$AH$32,ROW($1:$269)),ROW(1:1))
)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[Max]

One way to drive it out in cols AH and AI using non-array formulas
(requires just one tie-breaker column in say, col AJ)

Put in AH32:

=IF(ISERROR(LARGE($AJ$32:$AJ$300,ROWS($A$1:A1))),"",
INDEX(AF$32:AF$300,
MATCH(LARGE($AJ$32:$AJ$300,ROWS($A$1:A1)),$AJ$32:$AJ$300,0)))

Copy AH32 across to AI32

Put in AJ32: =IF(AF32="","",AF32-ROW()/10^10)

Select AH32:AJ32, fill down to AJ300

The above will yield the desired results
all neatly bunched at the top:

25 4
14 2
11 3
11 5
11 6

 

[Guest]

Hi Max,RagDyer,Domenic
Thank You for your imput and it certainly help make my life easier
thank again !

 

[Max]

You're welcome, Bill !
Thanks for the feedback ..