# Excel - looking up multiple records of multiple values



## holly00 (Jul 27, 2007)

Hi,
I have frequently used an array formula like this to look up multiple records in a list in Excel:
{=INDEX($A$1:$B$7),SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)}
It nicely retrieves all the rows where A10 is the value to be looked up.

However I have not had any success using the same type of formula with an OR statement:
{=INDEX($A$1:$B$7),SMALL(IF(OR($A$1:$A$7=$A$10,$A$1:$A$7=$A$11)),ROW($A$1:$A$7),ROW(1:1)),2)}

I want to retrieve all the records in a list where the value is what is as in A10 OR A11.

Does anyone know how I can achieve this?
Thanks!!!!!
Janie


----------



## Zack Barresse (Jul 25, 2004)

Without a workbook sample, maybe ...

=INDEX($A$1:$B$7),SMALL(IF(($A$1:$A$7=$A$10)*($A$1:$A$7=$A$11),ROW($A$1:$ A$7),ROW(1:1)),2)

.. please note I have not tested this, but if I'm following the logic right, the array-range should hold it. Let us know.


----------



## holly00 (Jul 27, 2007)

Thanks Firefytr!
I have tried the formula but couldn't get Excel to accept it. I am unsure about the placement of the brackets? I tried removing the red bracket and adding the green bracket instead:

=INDEX($A$1:$B$7),SMALL(IF(($A$1:$A$7=$A$10)*($A$1:$A$7=$A$11),ROW($A$1:$ A$7)),ROW(1:1)),2)

but I get a #NUM error even though the formula seems right in the formula result screen

I've attached a sample sheet where I am trying to get it to work. The new formula is in cell C17 down.

Thanks again!
Janie


----------



## Zack Barresse (Jul 25, 2004)

The red bracket you have above is not right. The formula logic itself is not right and where the problem lies. Basically, in the file you uploaded, you are looking at column A to see if the values meet BOTH the value of E1 and E2. Well, how can a single value equal two separate values at the same time? It can't, so you'll never return any valid numbers there.

So your formula would look like this ...

=INDEX($A$1:$B$13,SMALL(IF(($A$1:$A$13=$E$1)+($A$1:$A$13=$E$2),ROW($A$1:$A$13)),ROW(1:1)),2)

Notice something different? One single character is changed. Notice the *+* sign where there used to be a *** sign. This is because the *** sign is used for a logical AND situation, whilst the *+* sign is used for a logical OR situation. Seems to work for me.

HTH


----------



## holly00 (Jul 27, 2007)

Brilliant!!!!!!!!
Thanks so much for your help. It has worked perfectly!


----------



## Zack Barresse (Jul 25, 2004)

You're very welcome! Don't forget you can mark your thread as Solved by going to Thread Tools | Mark Solved | Perform Action. 

Take care!


----------

