Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using sumproduct to sum multiple colums | Excel Discussion (Misc queries) | |||
lock multiple rows and colums ? | Excel Discussion (Misc queries) | |||
vlookup colums with reference to a formula from another sheet | Excel Worksheet Functions | |||
match to colums vlookup | Excel Discussion (Misc queries) | |||
Sum mixed colums in multiple worksheets | Excel Worksheet Functions |