VLOOKUP for multiple colums

[MagicBill]

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?

 

[Pete_UK]

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

 

[Peo Sjoblom]

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(D19,MAX((A1:C9=I1)*(ROW(A1:C9))))

Where A:C9 hold the letters and D19 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

 

[T. Valko]

Try this array formula** :

=INDEX(D210,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)

 

[Stephen Lloyd]

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)

 

[Lars-Åke Aspelin]

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))*(D19))

Hope this helps / Lars-Åke

 

[Harlan Grove]

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 Ito
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

 

[StumpedAgain]

Here's one possible solution:

=IF(ISNA(VLOOKUP(F11,A19,4,FALSE)),IF(ISNA(VLOOKUP(F11,B19,3,FALSE)),VLOOKUP(F11,C19,2,FALSE),VLOOKUP(F11,B19,3,FALSE)),VLOOKUP(F11,A19,4,FALSE))

 

[Lars-Åke Aspelin]

Your formula does not fly Stephen.

Lars-Åke

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)

 

[T. Valko]

Based on the posted sample data:

=SUMPRODUCT((A3:C11=A15)*D311)

 

[Stephen Lloyd]

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