|
What's wrong with my array formula
In A1 I have
100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 2300 2400 2500 In D1 I have 200 400 600 800 1000 1200 1400 1600 1800 2000 2200 2400 In E1 I have A B C D E F G H I J K L I want to match the value in Col A with Col B and were equal copy from Col E that is next to the matching value in Col D to Col B next to the matching value in Col A. Did that make any sense?? Here what I'd hope for where the numbers are column A and the letters are in column B. 100 200 A 300 400 B 500 600 C 700 800 D 900 1000 E 1100 1200 F 1300 1400 G 1500 1600 H 1700 1800 I 1900 2000 J 2100 2200 K 2300 2400 L 2500 Here is the formula I tried but only worked in B2 B1 has this formula {=IF(A1=$D$1:$D$12,$E$1:$E$12,"")} B2 has this formula {=IF(A2=$D$1:$D$12,$E$1:$E$12,"")} B3 has this formula {=IF(A3=$D$1:$D$12,$E$1:$E$12,"")} etc. What have I done wrong? |
A simple Vlookup in cell B2 gives me the result that you are seeking.
=IF(ISNA(VLOOKUP(A2,$D$1:$E$12,2,0)),"",VLOOKUP(A2 ,$D$1:$E$12,2,0)) and copy it all the way down column B Is there something else that you want ? Am I missing something here? "M.Siler" wrote in message ... In A1 I have 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 2300 2400 2500 In D1 I have 200 400 600 800 1000 1200 1400 1600 1800 2000 2200 2400 In E1 I have A B C D E F G H I J K L I want to match the value in Col A with Col B and were equal copy from Col E that is next to the matching value in Col D to Col B next to the matching value in Col A. Did that make any sense?? Here what I'd hope for where the numbers are column A and the letters are in column B. 100 200 A 300 400 B 500 600 C 700 800 D 900 1000 E 1100 1200 F 1300 1400 G 1500 1600 H 1700 1800 I 1900 2000 J 2100 2200 K 2300 2400 L 2500 Here is the formula I tried but only worked in B2 B1 has this formula {=IF(A1=$D$1:$D$12,$E$1:$E$12,"")} B2 has this formula {=IF(A2=$D$1:$D$12,$E$1:$E$12,"")} B3 has this formula {=IF(A3=$D$1:$D$12,$E$1:$E$12,"")} etc. What have I done wrong? |
There's really no need for an array formula.
Try this Vlookup formula in B1, and copy down: =IF(ISNA(MATCH(A1,$D$1:$D$12,0)),"",VLOOKUP(A1,$D$ 1:$E$12,2,0)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "M.Siler" wrote in message ... In A1 I have 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 2300 2400 2500 In D1 I have 200 400 600 800 1000 1200 1400 1600 1800 2000 2200 2400 In E1 I have A B C D E F G H I J K L I want to match the value in Col A with Col B and were equal copy from Col E that is next to the matching value in Col D to Col B next to the matching value in Col A. Did that make any sense?? Here what I'd hope for where the numbers are column A and the letters are in column B. 100 200 A 300 400 B 500 600 C 700 800 D 900 1000 E 1100 1200 F 1300 1400 G 1500 1600 H 1700 1800 I 1900 2000 J 2100 2200 K 2300 2400 L 2500 Here is the formula I tried but only worked in B2 B1 has this formula {=IF(A1=$D$1:$D$12,$E$1:$E$12,"")} B2 has this formula {=IF(A2=$D$1:$D$12,$E$1:$E$12,"")} B3 has this formula {=IF(A3=$D$1:$D$12,$E$1:$E$12,"")} etc. What have I done wrong? |
I just learned about arrays a few weeks ago and I guess I had my head stuck
in arrary land. That works... thanks. "N Harkawat" wrote in message ... A simple Vlookup in cell B2 gives me the result that you are seeking. =IF(ISNA(VLOOKUP(A2,$D$1:$E$12,2,0)),"",VLOOKUP(A2 ,$D$1:$E$12,2,0)) and copy it all the way down column B Is there something else that you want ? Am I missing something here? "M.Siler" wrote in message ... In A1 I have 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 2300 2400 2500 In D1 I have 200 400 600 800 1000 1200 1400 1600 1800 2000 2200 2400 In E1 I have A B C D E F G H I J K L I want to match the value in Col A with Col B and were equal copy from Col E that is next to the matching value in Col D to Col B next to the matching value in Col A. Did that make any sense?? Here what I'd hope for where the numbers are column A and the letters are in column B. 100 200 A 300 400 B 500 600 C 700 800 D 900 1000 E 1100 1200 F 1300 1400 G 1500 1600 H 1700 1800 I 1900 2000 J 2100 2200 K 2300 2400 L 2500 Here is the formula I tried but only worked in B2 B1 has this formula {=IF(A1=$D$1:$D$12,$E$1:$E$12,"")} B2 has this formula {=IF(A2=$D$1:$D$12,$E$1:$E$12,"")} B3 has this formula {=IF(A3=$D$1:$D$12,$E$1:$E$12,"")} etc. What have I done wrong? |
I just learned about arrays a few weeks ago and I guess I had my head stuck
in arrary land. That works... thanks. "RagDyer" wrote in message ... There's really no need for an array formula. Try this Vlookup formula in B1, and copy down: =IF(ISNA(MATCH(A1,$D$1:$D$12,0)),"",VLOOKUP(A1,$D$ 1:$E$12,2,0)) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "M.Siler" wrote in message ... In A1 I have 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 2300 2400 2500 In D1 I have 200 400 600 800 1000 1200 1400 1600 1800 2000 2200 2400 In E1 I have A B C D E F G H I J K L I want to match the value in Col A with Col B and were equal copy from Col E that is next to the matching value in Col D to Col B next to the matching value in Col A. Did that make any sense?? Here what I'd hope for where the numbers are column A and the letters are in column B. 100 200 A 300 400 B 500 600 C 700 800 D 900 1000 E 1100 1200 F 1300 1400 G 1500 1600 H 1700 1800 I 1900 2000 J 2100 2200 K 2300 2400 L 2500 Here is the formula I tried but only worked in B2 B1 has this formula {=IF(A1=$D$1:$D$12,$E$1:$E$12,"")} B2 has this formula {=IF(A2=$D$1:$D$12,$E$1:$E$12,"")} B3 has this formula {=IF(A3=$D$1:$D$12,$E$1:$E$12,"")} etc. What have I done wrong? |
| All times are GMT +1. The time now is 10:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com