Lookup with multiple criteria and multiple results

[VinCieJ1988]

Hello all,

I am struggeling over a lookup with multiple criteria and results.
Currently I have the array formula working with one criteria and multiple results.

Now I want to search between two numbers and get all results between those numbers.
I tried the following but this is not working:

{=IF(ISERROR(INDEX($A$4:$C$7;SMALL(IF(ISNUMBER(SEARCH($A$10;$C$4:$C$7)*SEARCH($B$10;$C$4:$C$7));ROW($C$4:$C$7));ROW(1:1))-3;2));"No result";INDEX($A$4:$C$7;SMALL(IF(ISNUMBER(SEARCH($A$10;$C$4:$C$7)*SEARCH($B$10;$C$4:$C$7));ROW($C$4:$C$7));ROW(1:1))-3;2))}

Any ideas about how I can get this working?
I added the Excel file with the formula and table structure.

Many thanks,

Vincent

 

[Somendra Misra]

Hi Vincent,

If Search values are in ascending order, than try below array formula:

=IF(ISERROR(INDEX($B$4:$B$7,SMALL(IF($C$4:$C$7>=$A$10,IF($C$4:$C$7<=$B$10,ROW($C$4:$C$7)-ROW($C$4)+1)),ROWS($B$13:B13)))),"No Result",INDEX($B$4:$B$7,SMALL(IF($C$4:$C$7>=$A$10,IF($C$4:$C$7<=$B$10,ROW($C$4:$C$7)-ROW($C$4)+1)),ROWS($B$13:B13))))

I am assuming you are using Excel 2003 as you are using ISERROR function.

Regards,

 

[VinCieJ1988]

Thanks a lot!

This formula is working.
I am not using Excel 2003 but Excel 2013.

You are right about the ISERROR. I should change it with the IFERROR function.

Thanks for your advice and help.

 

[Somendra Misra]

Glad you solve it, Welcome Back Anytime.

Regards,