|
array formula problem
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(AF 32:AF300)),ROW(1:1)))} coppied down to 5 rows as 1 only need 5 results can some one please help ? greatly appreciated bill gras -- bill gras |
Try...
AI32, copied down: =INDEX($AG$32:$AG$44,SMALL(IF($AF$32:$AF$44=AH32,R OW($AG$32:$AG$44)-ROW($ AG$32)+1),COUNTIF($AH$32:AH32,AH32))) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , bill gras wrote: 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(AF 32:AF300)),ROW(1:1)))} coppied down to 5 rows as 1 only need 5 results can some one please help ? greatly appreciated bill gras |
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! ============================================== "bill gras" wrote in message ... 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(AF 32:AF300)),ROW(1:1)))} coppied down to 5 rows as 1 only need 5 results can some one please help ? greatly appreciated bill gras -- bill gras |
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! ============================================== "RagDyer" wrote in message ... 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! ============================================== "bill gras" wrote in message ... 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(AF 32:AF300)),ROW(1:1)))} coppied down to 5 rows as 1 only need 5 results can some one please help ? greatly appreciated bill gras -- bill gras |
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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "bill gras" wrote in message ... 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(AF 32:AF300)),ROW(1:1)))} coppied down to 5 rows as 1 only need 5 results can some one please help ? greatly appreciated bill gras -- bill gras |
Hi Max,RagDyer,Domenic
Thank You for your imput and it certainly help make my life easier thank again ! -- bill gras "Max" wrote: 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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "bill gras" wrote in message ... 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(AF 32:AF300)),ROW(1:1)))} coppied down to 5 rows as 1 only need 5 results can some one please help ? greatly appreciated bill gras -- bill gras |
You're welcome, Bill !
Thanks for the feedback .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "bill gras" wrote in message ... Hi Max,RagDyer,Domenic Thank You for your imput and it certainly help make my life easier thank again ! -- bill gras |
| All times are GMT +1. The time now is 01:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com