Return multiple values from a column based on multiple lookup values

[Ritvars_R]

Hi,

I have a list of look up values

Client A
Client B
Client C
Client D

I want to create a formula which would look up Client A, Client B, Client C, ... values in a range and return any corresponding values to these lookup values. The formula works if I try to look up only A value in a range and return the corresponding values, but I don't know how to add more lookup values to this formula. Please see the example formula below.

=IF(ISERROR(INDEX(Sheet7!$A$1:$C$1996,SMALL(IF(Sheet7!$A$1:$A$1996='Monday test'!$E$3&'Monday test'!$E$4,ROW(Sheet7!$A$1:$A$1996)),ROW(Sheet7!1:1)),2)),"",INDEX(Sheet7!$A$1:$G$1996,SMALL(IF(Sheet7!$A$1:$A$1996='Monday test'!$E$3&'Monday test'!$E$4,ROW(Sheet7!$A$1:$A$1996)),ROW(Sheet7!1:1)),2))

Many thanks for your help in advance.

Ritvars

 

[Ritvars_R]

After reading quite a few threads on the forum, I realised I wasn't particularly clear on what I am trying to achieve, so I will try to explain the problem more clearly below.

I have a range of look up values in Sheet1:
[TABLE="width: 100"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[/TR]
[TR]
[TD]Client C[/TD]
[/TR]
[TR]
[TD]Client D[/TD]
[/TR]
</tbody>[/TABLE]

Then I have data in a table in Sheet2:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]String1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]String2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]String3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]String4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client F[/TD]
[TD]String5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client C[/TD]
[TD]String6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client C[/TD]
[TD]String1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client C[/TD]
[TD]String3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]String 4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I want to achieve is in Sheet1 to have a following list:

[TABLE="width: 200"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]String1[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]String2[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]String3[/TD]
[/TR]
[TR]
[TD]Client A[/TD]
[TD]String4[/TD]
[/TR]
[TR]
[TD]Client B[/TD]
[TD]String4[/TD]
[/TR]
[TR]
[TD]Client C[/TD]
[TD]String6[/TD]
[/TR]
[TR]
[TD]Client C[/TD]
[TD]String1[/TD]
[/TR]
[TR]
[TD]Client C[/TD]
[TD]String3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


This is the formula I'm currently playing with as to find a solution to this, but with little success so far.

[COLOR=#574123]=IF(ISERROR(INDEX(Sheet7!$A$1:$C$1996,SMALL(IF(Sheet7!$A$1:$A$1996='Monday test'!$E$3&'Monday test'!$E$4,ROW(Sheet7!$A$1:$A$1996)),ROW(Sheet7!1:1)),2)),"",INDEX(Sheet7!$A$1:$G$1996,SMALL(IF(Sheet7!$A$1:$A$1996='Monday test'!$E$3&'Monday test'!$E$4,ROW(Sheet7!$A$1:$A$1996)),ROW(Sheet7!1:1)),2))[/COLOR]

Thank you for your help in advance!