Index / Match issue.

[Jing]

I have the following formula entered into K5

=IF(ROWS(K$5:K5)>$J$3,"",INDEX('Vancouver Listings'!$A$94:$A$474,MATCH($J$5,'Vancouver Listings'!$F$94:$F$474,0)+ROWS(K$5:K5)-1))

it is then copied down from K6:K93

the problem i am running into is that it will match J5 to the first match in the aray F94:F474. Then instead of finding another match of J5 after that, it just grabs the data in the in the cells directly below it for a total of the counted amount in Cell J3...

so say J3 is = to 14 as there are 14 matches to the data entered into J5. basically it will find the first match and place the correct value into K5. then just takes the next 14 cells below it and fill in K6:K18 with incorrect data...

I would like it to find each match within the aray F94:F474 and pull the data left of the match J5 in A94:A474

thank you in advance.

 

[T. Valko]

I have the following formula entered into K5

=IF(ROWS(K$5:K5)>$J$3,"",INDEX('Vancouver Listings'!$A$94:$A$474,MATCH($J$5,'Vancouver Listings'!$F$94:$F$474,0)+ROWS(K$5:K5)-1))

it is then copied down from K6:K93

the problem i am running into is that it will match J5 to the first match in the aray F94:F474. Then instead of finding another match of J5 after that, it just grabs the data in the in the cells directly below it for a total of the counted amount in Cell J3...

so say J3 is = to 14 as there are 14 matches to the data entered into J5. basically it will find the first match and place the correct value into K5. then just takes the next 14 cells below it and fill in K6:K18 with incorrect data...

I would like it to find each match within the aray F94:F474 and pull the data left of the match J5 in A94:A474

thank you in advance.

Try this array formula**:

=IF(ROWS(K$5:K5)>$J$3,"",INDEX('Vancouver Listings'!$A:$A,SMALL(IF('Vancouver Listings'!$F$94:$F$474=$J$5,ROW('Vancouver Listings'!$F$94:$F$474)),ROWS(K$5:K5))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

 

[Jing]

DUDE!!! thats awesome... Thank you

 

[T. Valko]

DUDE!!! thats awesome... Thank you

You're welcome. Thanks for the feedback!