Home |
Search |
Today's Posts |
#1
|
|||
|
|||
lookup
ok i really hope you can help me, i'll try to be more detailed as possible.
i have the following chart: column A column B columnn C unit# dept# new# 7112 101 725 7112 102 726 7112 103 727 7112 104 728 7112 105 729 7112 106 730 7113 101 311 7113 102 312 7113 103 313 7113 104 314 7113 105 315 7113 106 316 6880 101 725 6880 102 312 6880 103 313 6880 104 314 6880 105 729 6880 106 730 i need a formula that based upon the values in column A and B, returns the specific value in column C, the value in column C varies according to the location of the "unit" that's why i need a formula that takes in consideration both unit# and dept#. i hope somebody can help me. i really appreciate. Armando Martellini |
#2
|
|||
|
|||
Armando Martellini wrote:
ok i really hope you can help me, i'll try to be more detailed as possible. i have the following chart: column A column B columnn C unit# dept# new# 7112 101 725 7112 102 726 7112 103 727 7112 104 728 7112 105 729 7112 106 730 7113 101 311 7113 102 312 7113 103 313 7113 104 314 7113 105 315 7113 106 316 6880 101 725 6880 102 312 6880 103 313 6880 104 314 6880 105 729 6880 106 730 i need a formula that based upon the values in column A and B, returns the specific value in column C, the value in column C varies according to the location of the "unit" that's why i need a formula that takes in consideration both unit# and dept#. i hope somebody can help me. i really appreciate. Armando Martellini D2, copied down: =A2&"#"&B2 Then: [1] If you sort A:D on D in ascending order and can maintain A:D sorted, invoke: =IF(LOOKUP(X2&"#"&Y2,D:D)=X2&"#"&Y2,LOOKUP(X2&"#"& Y2,D:D,C:C),"") [2] If sorting is not a viable option, invoke: =INDEX(C:C,MATCH(X2&"#"&Y2,D:D,0)) Note that X2 houses a unit# and Y2 a dept# you want to look up. Addendum. If you are on Excel 2003, turn the range in A:D into a list by running the Data|List|Create List option and change the whole column references in [1] and [2] into exact/definite ranges. |
#3
|
|||
|
|||
thanks alot aladin, it worked great even without the 2nd part
"Aladin Akyurek" wrote: Armando Martellini wrote: ok i really hope you can help me, i'll try to be more detailed as possible. i have the following chart: column A column B columnn C unit# dept# new# 7112 101 725 7112 102 726 7112 103 727 7112 104 728 7112 105 729 7112 106 730 7113 101 311 7113 102 312 7113 103 313 7113 104 314 7113 105 315 7113 106 316 6880 101 725 6880 102 312 6880 103 313 6880 104 314 6880 105 729 6880 106 730 i need a formula that based upon the values in column A and B, returns the specific value in column C, the value in column C varies according to the location of the "unit" that's why i need a formula that takes in consideration both unit# and dept#. i hope somebody can help me. i really appreciate. Armando Martellini D2, copied down: =A2&"#"&B2 Then: [1] If you sort A:D on D in ascending order and can maintain A:D sorted, invoke: =IF(LOOKUP(X2&"#"&Y2,D:D)=X2&"#"&Y2,LOOKUP(X2&"#"& Y2,D:D,C:C),"") [2] If sorting is not a viable option, invoke: =INDEX(C:C,MATCH(X2&"#"&Y2,D:D,0)) Note that X2 houses a unit# and Y2 a dept# you want to look up. Addendum. If you are on Excel 2003, turn the range in A:D into a list by running the Data|List|Create List option and change the whole column references in [1] and [2] into exact/definite ranges. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup more than one cell | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |