| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Big Problem using VLOOKUP formula
Hi guys,
I am facing a tedious problem using the VLOOKUP formula. First of all I give to all a clear picture on what I want to do. For ex: A1 - 1 B1-100 C1-2 D1-500 E1-1 A2 - 3 B2-200 C2-4 D2-600 E2-2 A2 - 5 B3-300 C3-6 D3-700 E3-3 A2 - 7 B4-400 C4-8 D4-800 E4-4 I have to create a coloumn F that contains the values contained on the coloumn B or C depending if the values on E are found in the A or C coloumns. So F should be: F1-100 F2-500 F3-200 F4-600 ..... I have created this formula: IF(ISNA(VLOOKUP(F1,$A$1:$B$4,2,FALSE)),VLOOKUP(F1, $C$1:$D$4,4,FALSE),VLOOKUP(F1,$A$1:$B$4,2,FALSE)) But in the case of F2 is not working.. It seems that even if I select as table_array the range C1:D4 is always checking in the coloumn A. Anyone could help me to understand how to solve this problem? I hope is clear. Thanks. BR, Jessie |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Big Problem using VLOOKUP formula
Try something like this:
=IF(isna(MATCH(E1,$A$1:$A$4,0)),INDEX($D$1:$D$4,MA TCH(E1,$C$1:$C$4,0),1),INDEX($D$1:$D$4,MATCH(E1,$A $1:$A$4,0),1)) regards Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "jessie" wrote in message ps.com... Hi guys, I am facing a tedious problem using the VLOOKUP formula. First of all I give to all a clear picture on what I want to do. For ex: A1 - 1 B1-100 C1-2 D1-500 E1-1 A2 - 3 B2-200 C2-4 D2-600 E2-2 A2 - 5 B3-300 C3-6 D3-700 E3-3 A2 - 7 B4-400 C4-8 D4-800 E4-4 I have to create a coloumn F that contains the values contained on the coloumn B or C depending if the values on E are found in the A or C coloumns. So F should be: F1-100 F2-500 F3-200 F4-600 .... I have created this formula: IF(ISNA(VLOOKUP(F1,$A$1:$B$4,2,FALSE)),VLOOKUP(F1, $C$1:$D$4,4,FALSE),VLOOKUP(F1,$A$1:$B$4,2,FALSE)) But in the case of F2 is not working.. It seems that even if I select as table_array the range C1:D4 is always checking in the coloumn A. Anyone could help me to understand how to solve this problem? I hope is clear. Thanks. BR, Jessie |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Big Problem using VLOOKUP formula
Charles using your formula the result of coloumn F is not the expected
one but the following: F1-500 F2-500 F3-600 F4-600 ...... What's worng? Thanks for your help. BR, Jessie Charles Williams wrote: Try something like this: =IF(isna(MATCH(E1,$A$1:$A$4,0)),INDEX($D$1:$D$4,MA TCH(E1,$C$1:$C$4,0),1),INDEX($D$1:$D$4,MATCH(E1,$A $1:$A$4,0),1)) regards Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "jessie" wrote in message ps.com... Hi guys, I am facing a tedious problem using the VLOOKUP formula. First of all I give to all a clear picture on what I want to do. For ex: A1 - 1 B1-100 C1-2 D1-500 E1-1 A2 - 3 B2-200 C2-4 D2-600 E2-2 A2 - 5 B3-300 C3-6 D3-700 E3-3 A2 - 7 B4-400 C4-8 D4-800 E4-4 I have to create a coloumn F that contains the values contained on the coloumn B or C depending if the values on E are found in the A or C coloumns. So F should be: F1-100 F2-500 F3-200 F4-600 .... I have created this formula: IF(ISNA(VLOOKUP(F1,$A$1:$B$4,2,FALSE)),VLOOKUP(F1, $C$1:$D$4,4,FALSE),VLOOKUP(F1,$A$1:$B$4,2,FALSE)) But in the case of F2 is not working.. It seems that even if I select as table_array the range C1:D4 is always checking in the coloumn A. Anyone could help me to understand how to solve this problem? I hope is clear. Thanks. BR, Jessie |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Big Problem using VLOOKUP formula
I found the error, the correct formula is:
=IF(ISNA(MATCH(E1,$A$1:$A$4,0)),INDEX($D$1:$D$4,MA TCH(E1,$C$1:$C$4,0),1),INDEX($B$1:$B$4,MATCH(E1,$A $1:$A$4,0),1)) Thanks again BR, Jessie jessie wrote: Charles using your formula the result of coloumn F is not the expected one but the following: F1-500 F2-500 F3-600 F4-600 ..... What's worng? Thanks for your help. BR, Jessie Charles Williams wrote: Try something like this: =IF(isna(MATCH(E1,$A$1:$A$4,0)),INDEX($D$1:$D$4,MA TCH(E1,$C$1:$C$4,0),1),INDEX($D$1:$D$4,MATCH(E1,$A $1:$A$4,0),1)) regards Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "jessie" wrote in message ps.com... Hi guys, I am facing a tedious problem using the VLOOKUP formula. First of all I give to all a clear picture on what I want to do. For ex: A1 - 1 B1-100 C1-2 D1-500 E1-1 A2 - 3 B2-200 C2-4 D2-600 E2-2 A2 - 5 B3-300 C3-6 D3-700 E3-3 A2 - 7 B4-400 C4-8 D4-800 E4-4 I have to create a coloumn F that contains the values contained on the coloumn B or C depending if the values on E are found in the A or C coloumns. So F should be: F1-100 F2-500 F3-200 F4-600 .... I have created this formula: IF(ISNA(VLOOKUP(F1,$A$1:$B$4,2,FALSE)),VLOOKUP(F1, $C$1:$D$4,4,FALSE),VLOOKUP(F1,$A$1:$B$4,2,FALSE)) But in the case of F2 is not working.. It seems that even if I select as table_array the range C1:D4 is always checking in the coloumn A. Anyone could help me to understand how to solve this problem? I hope is clear. Thanks. BR, Jessie |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Big Problem using VLOOKUP formula
Oops, well done to fix it.
regards Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "jessie" wrote in message ups.com... I found the error, the correct formula is: =IF(ISNA(MATCH(E1,$A$1:$A$4,0)),INDEX($D$1:$D$4,MA TCH(E1,$C$1:$C$4,0),1),INDEX($B$1:$B$4,MATCH(E1,$A $1:$A$4,0),1)) Thanks again BR, Jessie jessie wrote: Charles using your formula the result of coloumn F is not the expected one but the following: F1-500 F2-500 F3-600 F4-600 ..... What's worng? Thanks for your help. BR, Jessie Charles Williams wrote: Try something like this: =IF(isna(MATCH(E1,$A$1:$A$4,0)),INDEX($D$1:$D$4,MA TCH(E1,$C$1:$C$4,0),1),INDEX($D$1:$D$4,MATCH(E1,$A $1:$A$4,0),1)) regards Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "jessie" wrote in message ps.com... Hi guys, I am facing a tedious problem using the VLOOKUP formula. First of all I give to all a clear picture on what I want to do. For ex: A1 - 1 B1-100 C1-2 D1-500 E1-1 A2 - 3 B2-200 C2-4 D2-600 E2-2 A2 - 5 B3-300 C3-6 D3-700 E3-3 A2 - 7 B4-400 C4-8 D4-800 E4-4 I have to create a coloumn F that contains the values contained on the coloumn B or C depending if the values on E are found in the A or C coloumns. So F should be: F1-100 F2-500 F3-200 F4-600 .... I have created this formula: IF(ISNA(VLOOKUP(F1,$A$1:$B$4,2,FALSE)),VLOOKUP(F1, $C$1:$D$4,4,FALSE),VLOOKUP(F1,$A$1:$B$4,2,FALSE)) But in the case of F2 is not working.. It seems that even if I select as table_array the range C1:D4 is always checking in the coloumn A. Anyone could help me to understand how to solve this problem? I hope is clear. Thanks. BR, Jessie |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup array formula problem | Excel Worksheet Functions | |||
| Problem with using Vlookup formula | Excel Worksheet Functions | |||
| Vlookup/If? Formula problem? | New Users to Excel | |||
| Problem with VLOOKUP formula | Excel Worksheet Functions | |||
| Vlookup, What is correct formula for problem below? | Excel Worksheet Functions | |||
Linear Mode
