vlookup against one of two options?

[snakey]

Hi,
In Excel 2003 I am trying to return a unique value against 2 possible options.
I have a table of reference;
Col A Col B Col C
W (W) 3
1 (1) 9
1c (1c) 6
1b (1b) 9
1a (1a) 11
2 (2) 15
2c (2c) 13

etc

In the s/sheet used, a value from Col A or Col B will be entered so I want a
formula in the next column to return the relevant value from Col C. (eg, if
1a or (1a) = 11).

Greatful for any advice.
Cheers
snakey

 

[Eduardo]

Hi,
you enter your references in E1 and F1 and in G1 enter

=sumproduct((E1=$A$1:$A$1000),(F1=$B$1:$B$1000),$C$1:$C$1000)

 

[David Biddulph]

Perhaps instead of
=sumproduct((E1=$A$1:$A$1000),(F1=$B$1:$B$1000),$C$1:$C$1000)
you may have intended either
=sumproduct(--(E1=$A$1:$A$1000),--(F1=$B$1:$B$1000),$C$1:$C$1000)
or
=sumproduct((E1=$A$1:$A$1000)*(F1=$B$1:$B$1000)*$C$1:$C$1000) ?

 

[T. Valko]

Try this:

A10 = criteria cell

Note that the criteria cell must be formatted as TEXT. If you try to enter
something like (2) Excel, thinking it's helping you, will convert (2) to -2.

=SUMIF(A1:A7,SUBSTITUTE(SUBSTITUTE(A10,"(",""),")",""),C1:C7)