What's wrong with this Index Match/Row formula?

Hi,

I have an index match formula which works well in returning values from the first Column from a range (Columns G:K) if Column K contains and X The formula once copied down return all instances (2 instances below) and shows a blank for error handling when it reaches the last data to return.

ColumnG ColumnK

9130 X

2135

6859 X

=IF(ISERROR(INDEX(G:K,SMALL(IF(Sheet1!K:K="X",ROW(Sheet1!K:K)),ROW(1:1)),1)),"",INDEX(G:K,SMALL(IF(Sheet1!K:K="X",ROW(Sheet1!K:K)),ROW(1:1)),1))

I'm trying to modify the formula to move to a new range (Columns N:R) when it finishes finding data from the first range. The formula I thought would work is below, excel isn't accepting it and I can't work out what's wrong.

=IF((ISERROR(INDEX(G:K,SMALL(IF(Sheet1!K:K="X",ROW(Sheet1!K:K)),ROW(1:1)),1)),INDEX(N:R,SMALL(IF(Sheet1!R:R="X",ROW(Sheet1!R:R)),ROW(1:1)),1)),"")

My aim to to have it run through upwards of 5 ranges of data, but I obviously can't proceed until I can get it working with two.

Any help on this would be appreciated!

Thanks

It's harder to tell without an example of your worksheet, but try this:

=IF((ISERROR(INDEX(G:K,SMALL(IF(Sheet1!K:K="X",ROW(Sheet1!K:K)),ROW(1:1)),1))),INDEX(N:R,SMALL(IF(Sheet1!R:R="X",ROW(Sheet1!R:R)),ROW(1:1)),1),"")

I think you may have made a mistake with bracket placement.

Hi cffndncr,

It now seems to be returning values from the second range only, but not the first. I'll try and attach a sample to help.

Thanks

Sample.xlsx

cffndncr, I've attached a sample with the colours being the rangers (currently 2) but ultimately more. The formula is to return the value in column G and N where an X appears in K or R respectively.

I hope this makes a little sense.

Cheers

Thanks daddylonglegs,

I've modified the formula (as I have it in a seperate sheet and it seems to work perfectly) so thanks.

However I'm trying to add the 3rd range and potentially there may be 5 in total but it's giving me an error saying I've entered too many arguements.... Do I have the syntax in the correct order?



=IFERROR(IFERROR(INDEX('Org Structure'!G$7:G$553,SMALL(IF('Org Structure'!K$7:K$553="X",ROW('Org Structure'!K$7:K$553)-ROW('Org Structure'!K$7)+1),ROWS(A$2:A2))),INDEX('Org Structure'!N$7:N$553,SMALL(IF('Org Structure'!R$7:R$553="X",ROW('Org Structure'!R$7:R$553)-ROW('Org Structure'!R$7)+1),ROWS(A$2:A2)-COUNTIF('Org Structure'!K$7:K$553,"x"))),INDEX('Org Structure'!U$7:U$553,SMALL(IF('Org Structure'!Y$7:Y$553="X",ROW('Org Structure'!Y$7:Y$553)-ROW('Org Structure'!Y$7)+1),ROWS(A$2:A2)-COUNTIF('Org Structure'!R$7:R$553,"x")))),"")

Hey daddylonglegs, any idea what I've stuffed up by adding the third range?

=IFERROR(IFERROR(INDEX('Org Structure'!G$7:G$553,SMALL(IF('Org Structure'!K$7:K$553="X",ROW('Org Structure'!K$7:K$553)-ROW('Org Structure'!K$7)+1),ROWS(A$2:A2))),INDEX('Org Structure'!N$7:N$553,SMALL(IF('Org Structure'!R$7:R$553="X",ROW('Org Structure'!R$7:R$553)-ROW('Org Structure'!R$7)+1),ROWS(A$2:A2)-COUNTIF('Org Structure'!K$7:K$553,"x"))),INDEX('Org Structure'!U$7:U$553,SMALL(IF('Org Structure'!Y$7:Y$553="X",ROW('Org Structure'!Y$7:Y$553)-ROW('Org Structure'!Y$7)+1),ROWS(A$2:A2)-COUNTIF('Org Structure'!R$7:R$553,"x")))),"")

Any help would be appreciated as I'm stuck!

Thanks

Hi daddylonglegs, I've now expanded the formula to include a forth range and it works just as needed.

Many thanks for your help on the formula and syntax.

Cheers

David