VLOOKUP for multiple colums
 

I am trying to return a number (1-9) for a letter.

For instance:

A B C D

A J S 1
B K T 2
C L U 3
D M V 4
E N W 5
F O X 6
G P Y 7
H Q Z 8
I R 9

I can do the first column, but when I try the second or third columns, I get
an error value. So how can I type in a W and get it to return a 5, an I to
return a 9, and so on?

VLOOKUP for multiple colums
 

With your letter in A1, put this in B1 to get the appropriate value:

=MOD(CODE(UPPER(A1))-65,9)+1

There is no error checking beyond catching letters a-z.

Hope this helps.

Pete


On Jul 25, 5:49*pm, MagicBill
wrote:
I am trying to return a number (1-9) for a letter.

For instance:

A * * B * * C * * D

A * * J * * *S * * 1
B * * K * * *T * * 2
C * * L * * *U * * 3
D * * M * * V * * 4
E * * N * * W * * 5
F * * O * * X * * *6
G * * P * * *Y * * *7
H * * Q * * Z * * *8
I * * *R * * * * * * 9

I can do the first column, but when I try the second or third columns, I get
an error value. *So how can I type in a W and get it to return a 5, an I to
return a 9, and so on?

VLOOKUP for multiple colums
 

You would need 3 nested vlookups in an IF function testing first column,
then second then third


in one fell swoop you can use


=INDEX(D1:D9,MAX((A1:C9=I1)*(ROW(A1:C9))))

Where A:C9 hold the letters and D1:D9 hold the numbers.
It's an array formula and needs to be entered with ctrl + shift & enter


I would advice against using this layout, it is not a good spreadsheet
design

--


Regards,


Peo Sjoblom

"MagicBill" wrote in message
...
I am trying to return a number (1-9) for a letter.

For instance:

A B C D

A J S 1
B K T 2
C L U 3
D M V 4
E N W 5
F O X 6
G P Y 7
H Q Z 8
I R 9

I can do the first column, but when I try the second or third columns, I
get
an error value. So how can I type in a W and get it to return a 5, an I
to
return a 9, and so on?

VLOOKUP for multiple colums
 

Try this array formula** :

=INDEX(D2:D10,MAX((A2:C10=A1)*ROW(A2:C10)-MIN(ROW(A2:C10))+1))

Where A1 = lookup value

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"MagicBill" wrote in message
...
I am trying to return a number (1-9) for a letter.

For instance:

A B C D

A J S 1
B K T 2
C L U 3
D M V 4
E N W 5
F O X 6
G P Y 7
H Q Z 8
I R 9

I can do the first column, but when I try the second or third columns, I
get
an error value. So how can I type in a W and get it to return a 5, an I
to
return a 9, and so on?

VLOOKUP for multiple colums
 

To use Vlookup you'll need to extend your first column to include the whole
alphabet a-z and repeat the numbers in the second column. Vlookup can only
search a single column.

If the layout you show needs to stay as is you could do the following:

Lets say you're typing a letter into cell A15 and want B15 to display the
associated number value. In b15 write...

=sumproduct(--(a3:a11=A15),--(b3:b11=a15),--(c3:c11=a15),D3:d11)



"MagicBill" wrote:

I am trying to return a number (1-9) for a letter.

For instance:

A B C D

A J S 1
B K T 2
C L U 3
D M V 4
E N W 5
F O X 6
G P Y 7
H Q Z 8
I R 9

I can do the first column, but when I try the second or third columns, I get
an error value. So how can I type in a W and get it to return a 5, an I to
return a 9, and so on?

VLOOKUP for multiple colums
 

On Fri, 25 Jul 2008 09:49:06 -0700, MagicBill
wrote:

I am trying to return a number (1-9) for a letter.

For instance:

A B C D

A J S 1
B K T 2
C L U 3
D M V 4
E N W 5
F O X 6
G P Y 7
H Q Z 8
I R 9

I can do the first column, but when I try the second or third columns, I get
an error value. So how can I type in a W and get it to return a 5, an I to
return a 9, and so on?


If your input is in cell E1 you can try the following formula:
(Note: This is an array formula and must be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER)

=SUM(((E1=A1:C9))*(D1:D9))

Hope this helps / Lars-Åke

VLOOKUP for multiple colums
 

MagicBill wrote...
I am trying to return a number (1-9) for a letter.

For instance:
...
A J S 1
B K T 2
C L U 3
D M V 4
E N W 5
F O X 6
G P Y 7
H Q Z 8
I R 9

I can do the first column, but when I try the second or third columns, I get
an error value. *So how can I type in a W and get it to return a 5, an I to
return a 9, and so on?

If this is really just a simple single letter lookup, you've already
received two working answers. OTOH, if this is an oversimplified
example and you need to use different characters or strings, then if
your table above were in A1:C9, try the array formula

=MAX((A1:C9=entry_cell_reference_here)*ROW(A1:C9))-MIN(ROW(A1:C9))+1

VLOOKUP for multiple colums
 

Here's one possible solution:

=IF(ISNA(VLOOKUP(F11,A1:D9,4,FALSE)),IF(ISNA(VLOOK UP(F11,B1:D9,3,FALSE)),VLOOKUP(F11,C1:D9,2,FALSE), VLOOKUP(F11,B1:D9,3,FALSE)),VLOOKUP(F11,A1:D9,4,FA LSE))

--
-SA


"MagicBill" wrote:

I am trying to return a number (1-9) for a letter.

For instance:

A B C D

A J S 1
B K T 2
C L U 3
D M V 4
E N W 5
F O X 6
G P Y 7
H Q Z 8
I R 9

I can do the first column, but when I try the second or third columns, I get
an error value. So how can I type in a W and get it to return a 5, an I to
return a 9, and so on?

VLOOKUP for multiple colums
 

Your formula does not fly Stephen.

Lars-Åke


On Fri, 25 Jul 2008 10:07:01 -0700, Stephen Lloyd
wrote:

To use Vlookup you'll need to extend your first column to include the whole
alphabet a-z and repeat the numbers in the second column. Vlookup can only
search a single column.

If the layout you show needs to stay as is you could do the following:

Lets say you're typing a letter into cell A15 and want B15 to display the
associated number value. In b15 write...

=sumproduct(--(a3:a11=A15),--(b3:b11=a15),--(c3:c11=a15),D3:d11)



"MagicBill" wrote:

I am trying to return a number (1-9) for a letter.

For instance:

A B C D

A J S 1
B K T 2
C L U 3
D M V 4
E N W 5
F O X 6
G P Y 7
H Q Z 8
I R 9

I can do the first column, but when I try the second or third columns, I get
an error value. So how can I type in a W and get it to return a 5, an I to
return a 9, and so on?

VLOOKUP for multiple colums
 

Based on the posted sample data:

=SUMPRODUCT((A3:C11=A15)*D3:D11)

--
Biff
Microsoft Excel MVP


"Lars-Åke Aspelin" wrote in message
...
Your formula does not fly Stephen.

Lars-Åke


On Fri, 25 Jul 2008 10:07:01 -0700, Stephen Lloyd
wrote:

To use Vlookup you'll need to extend your first column to include the
whole
alphabet a-z and repeat the numbers in the second column. Vlookup can
only
search a single column.

If the layout you show needs to stay as is you could do the following:

Lets say you're typing a letter into cell A15 and want B15 to display the
associated number value. In b15 write...

=sumproduct(--(a3:a11=A15),--(b3:b11=a15),--(c3:c11=a15),D3:d11)



"MagicBill" wrote:

I am trying to return a number (1-9) for a letter.

For instance:

A B C D

A J S 1
B K T 2
C L U 3
D M V 4
E N W 5
F O X 6
G P Y 7
H Q Z 8
I R 9

I can do the first column, but when I try the second or third columns, I
get
an error value. So how can I type in a W and get it to return a 5, an I
to
return a 9, and so on?

VLOOKUP for multiple colums
 

Oh, durrr.. you're right that was silly of me to think that something
multiplied by zero would be anything other than zero.

"Lars-Ã…ke Aspelin" wrote:

Your formula does not fly Stephen.

Lars-Ã…ke


On Fri, 25 Jul 2008 10:07:01 -0700, Stephen Lloyd
wrote:

To use Vlookup you'll need to extend your first column to include the whole
alphabet a-z and repeat the numbers in the second column. Vlookup can only
search a single column.

If the layout you show needs to stay as is you could do the following:

Lets say you're typing a letter into cell A15 and want B15 to display the
associated number value. In b15 write...

=sumproduct(--(a3:a11=A15),--(b3:b11=a15),--(c3:c11=a15),D3:d11)



"MagicBill" wrote:

I am trying to return a number (1-9) for a letter.

For instance:

A B C D

A J S 1
B K T 2
C L U 3
D M V 4
E N W 5
F O X 6
G P Y 7
H Q Z 8
I R 9

I can do the first column, but when I try the second or third columns, I get
an error value. So how can I type in a W and get it to return a 5, an I to
return a 9, and so on?