vlookup against one of two options?
 

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

vlookup against one of two options?
 

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)

"snakey" wrote:

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

vlookup against one of two options?
 

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) ?
--
David Biddulph

"Eduardo" wrote in message
...
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)

"snakey" wrote:

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

vlookup against one of two options?
 

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)

--
Biff
Microsoft Excel MVP


"snakey" wrote in message
...
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