Search on text

[uivandals]

I need to firure out how to find a value based on 2 conditions that are text. Somewhat like this: If customer "acme" is in the central division and in state Alaska, return legal entity Newco. Anyone know what formula will do that?

 

[barry houdini]

Assuming you have a table with customers in column A state in B and "legal entities" in column C then try

=INDEX(C$2:C$100,MATCH(1,(A$2:A$100="acme")*(B$2:B$100="Alaska"),0))

confirmed with CTRL+SHIFT+ENTER

 

[uivandals]

Is it possible to use cell references instead of the text acme and alaska?

 

[Richard Schollar]

Yep.

 

[barry houdini]

Yes, that would be the preferred method, if customer is in F2 and state in G2

=INDEX(C$2:C$100,MATCH(1,(A$2:A$100=F2)*(B$2:B$100=G2),0))

 

[uivandals]

That's the formula that I have and it is returning #N/A.

=INDEX(Sheet1!$C1:$C5000,MATCH(1,(Sheet1!$E1:$E5000=B5)*(Sheet1!$B1:$B5000=$D:$D),0))

Column c on sheet 1 is legal entity
Column e on sheet 1 is division
Column b on sheet 1 is state

 

[barry houdini]

This part

Sheet1!$B1:$B5000=$D:$D

refers to the whole of column D, shouldn't this be a single cell (containing a specific state)?

Make sure you confirm formula with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

 

[Richard Schollar]

You can't use whole column refs in an array formula so the $D:$D bit will cause failure. Shouldn't this be a single cell reference in any case?

 

[uivandals]

Yep, that was the issue, thanks. If I needed to add a third condition, how would that change the formula?

 

[barry houdini]

IF you also want column F to equal D5 try

=INDEX(Sheet1!$C1:$C5000,MATCH(1,(Sheet1!$E1:$E5000=B5)*(Sheet1!$B1:$B5000=C5)*(Sheet1!$F1:$F5000=D5),0))