VLOOKUP for multiple colums

  • Thread starter Thread starter MagicBill
  • Start date Start date
M

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?
 
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
 
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
 
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)
 
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)
 
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?
Click to expand...


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
 
MagicBill said:
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?
Click to expand...

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
 
Here's one possible solution:

=IF(ISNA(VLOOKUP(F11,A1:D9,4,FALSE)),IF(ISNA(VLOOKUP(F11,B1:D9,3,FALSE)),VLOOKUP(F11,C1:D9,2,FALSE),VLOOKUP(F11,B1:D9,3,FALSE)),VLOOKUP(F11,A1:D9,4,FALSE))
 
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)
Click to expand...
 
Oh, durrr.. you're right that was silly of me to think that something
multiplied by zero would be anything other than zero.
 
Back
Top