Look up multiple values and return multiple corresponding values

[copperbum]

Hi,

I know how to return multiple corresponding values from a list based on one value, however now I am trying to return all the corresponding values from a list based on multiple values.

My original code is as follows:

=INDEX('FIC Info'!$A$1:$P$512,SMALL(IF('FIC Info'!$A$1:$A$512=$I$2,ROW('FIC Info'!$A$1:$A$512)),ROW('FIC Info'!1:1)),$A$11)

Where FIC Info is a tab with all of my data, I2 is the lookup value selected from a drop down list, and A11 is the column number based on the value of another drop down list.

I have been trying to alter the lookup value from one value to a series of values based on a table of data on a new tab. I have tried using a series of if statements that reads something like:

....SMALL(IF('FIC Info'!A$1:$A$512=IF($I$2='New Tab'!$A$1,'New Tab'!$A$2:$A$5,IF($I$2='New Tab'!$B$1,'New Tab'!$B$2:$B$5,IF(....

This produced an error, and I couldn't get it to work, so I tried using an OR function to replace the arrays:

....SMALL(IF('FIC Info'!A$1:$A$512=IF($I$2='New Tab'!$A$1,OR($A$2,$A$3,$A$4,$A5),IF($I$2='New Tab'!$B$1,OR($B$2,$BB$3,$B$4,$B$5),IF(....

Again, this produced an error, and has left me out of ideas. If anyone could provide some assistance it would be greatly appreciated.

Thanks,
Nick.

 

[T. Valko]

Hi,

I know how to return multiple corresponding values from a list based on one value, however now I am trying to return all the corresponding values from a list based on multiple values.

My original code is as follows:

=INDEX('FIC Info'!$A$1:$P$512,SMALL(IF('FIC Info'!$A$1:$A$512=$I$2,ROW('FIC Info'!$A$1:$A$512)),ROW('FIC Info'!1:1)),$A$11)

Where FIC Info is a tab with all of my data, I2 is the lookup value selected from a drop down list, and A11 is the column number based on the value of another drop down list.

I have been trying to alter the lookup value from one value to a series of values based on a table of data on a new tab. I have tried using a series of if statements that reads something like:

....SMALL(IF('FIC Info'!A$1:$A$512=IF($I$2='New Tab'!$A$1,'New Tab'!$A$2:$A$5,IF($I$2='New Tab'!$B$1,'New Tab'!$B$2:$B$5,IF(....

This produced an error, and I couldn't get it to work, so I tried using an OR function to replace the arrays:

....SMALL(IF('FIC Info'!A$1:$A$512=IF($I$2='New Tab'!$A$1,OR($A$2,$A$3,$A$4,$A5),IF($I$2='New Tab'!$B$1,OR($B$2,$BB$3,$B$4,$B$5),IF(....

Again, this produced an error, and has left me out of ideas. If anyone could provide some assistance it would be greatly appreciated.

Thanks,
Nick.

Maybe like this...

...SMALL(IF(ISNUMBER(MATCH('FIC Info'!A$1:$A$512,IF($I$2='New Tab'!$A$1,'New Tab'!$A$2:$A$5,IF($I$2='New Tab'!$B$1,'New Tab'!$B$2:$B$5)),0)),...