Combine row and array in Index Match lookup

[Martijn]

Hi Excel Experts,

We use excel to create sample XML messages and the transformations that need to occur between them. First we specify the fields and transformations to create requests and replies and output predictions, then scripts are used to make the files and create a SoapUI project.

Usually one call will result in one reply so one column on one sheet matches with the same column on another sheet but usually a different row. In this case however the results of several calls are used to create a single reply. I've tried very hard to to come up with a formula to find the right row and column but so far I've been unable to and I hope you know a solution.

My index - match attempts didn't work out well so I haven't included a formula. My problem is how to combine a lookup in an array of fields and values to see which value is true with a lookup in a single row of testcase numbers. Together this will form the X coördinate of the Index lookup.

Can anyone help me do this?

 

[bosco_yip]

Try,

1] Select "Sheet1" B5 >> Define Name >>

Name : ColNo

Refers to: =INDEX(AGGREGATE(15,6,COLUMN($A$1:$D$1)/SEARCH(B$3,Sheet2!$B$3:$E$3),ROW(INDIRECT("1:"&COUNTIF(Sheet2!$B$3:$E$3,B$3&"*")))),ROWS($1:1))

>>OK

2] Then,

In "Sheet 1" B5, enter formula copied across right to D5, and all copied down :

=IFERROR(INDEX(Sheet2!$B$4:$E$7,SUMPRODUCT((INDEX(Sheet2!$B$4:$E$7,0,ColNo)=TRUE)*{1;2;3;4})+1,ColNo),"-")

Regards
Bosco

 

[Martijn]

Hi Bosco,

Thank you for your reply! After some tinkering I got your formula to work in my project.

Unfortunately, due of the number of fields involved and the use of an indirect formula Excel is slowing down to the point where an hourglass appears after typing in a cell, and I haven't even added this to all fields involved yet.

Is there a way around this?

Regards,

Martijn

 

[bosco_yip]

To instead of

Hi Bosco,

Thank you for your reply! After some tinkering I got your formula to work in my project.

Unfortunately, due of the number of fields involved and the use of an indirect formula Excel is slowing down to the point where an hourglass appears after typing in a cell, and I haven't even added this to all fields involved yet.

Is there a way around this?

Regards,

Martijn

To use Index function instead of Indirect function

The range name "ColNo" formula change from this :

=INDEX(AGGREGATE(15,6,COLUMN($A$1:$D$1)/SEARCH(B$3,Sheet2!$B$3:$E$3),ROW(INDIRECT("1:"&COUNTIF(Sheet2!$B$3:$E$3,B$3&"*")))),ROWS($1:1))

Into this :

=INDEX(AGGREGATE(15,6,COLUMN($A$1:$D$1)/SEARCH(B$3,Sheet2!$B$3:$E$3),ROW(INDEX($A:$A,1):INDEX($A:$A,COUNTIF(Sheet2!$B$3:$E$3,B$3&"*")))),ROWS($1:1))

p.s. revision highlighted in red color

Regards
Bosco

 

[Martijn]

Yes, it's much faster now. Thanks!