lookup

[Armando Martellini]

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

 

[Aladin Akyurek]

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 on D in ascending order and can maintain A sorted,
invoke:

=IF(LOOKUP(X2&"#"&Y2,D)=X2&"#"&Y2,LOOKUP(X2&"#"&Y2,D,C:C),"")

[2] If sorting is not a viable option, invoke:

=INDEX(C:C,MATCH(X2&"#"&Y2,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 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.

 

[Armando Martellini]

thanks alot aladin, it worked great even without the 2nd part

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 on D in ascending order and can maintain A sorted,
invoke:

=IF(LOOKUP(X2&"#"&Y2,D)=X2&"#"&Y2,LOOKUP(X2&"#"&Y2,D,C:C),"")

[2] If sorting is not a viable option, invoke:

=INDEX(C:C,MATCH(X2&"#"&Y2,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 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.