I have multiple sheets in one XLS file. Some 4-character codes repeat on
different sheets. I am trying to figure out how I can reference one sheet's
4-character code to suse the same descriptor in another sheet so there is
consistency. A sample follows:

A B
Descriptor 1 AAAA
Descriptor 2 AAAB
Descriptor 3 AAAC
Descriptor 4 BBBB
Descriptor 5 BBBA

I would like to be able to put in AAAC (col B) in another sheet and have
Descriptor 3 returned to me in column A.

I want to believe this is possible, I just haven't been able to figure it out.

Hi Scott,

You can use VLOOKUP if the code you are looking up is in the first column of
the lookup range, but as shown below your's is not. So you can use something
like MATCH and OFFSET or MATCH and INDEX or even MATCH and INDIRECT.

Suppose the table you showed below starts in cell A1

A B
Descriptor 1 AAAA
Descriptor 2 AAAB
Descriptor 3 AAAC
Descriptor 4 BBBB
Descriptor 5 BBBA

is in Sheet1 and on Sheet2 in cell A2 you enter AAAC and you want Descriptor
3 to appear in cell B2, then in B2 enter the formula

=INDEX(Sheet1!$A$1:$A$5,MATCH(A2,Sheet1!$B$1:$B$5, ))
--
Thanks,
Shane Devenshire


"Scott A" wrote:

I have multiple sheets in one XLS file. Some 4-character codes repeat on
different sheets. I am trying to figure out how I can reference one sheet's
4-character code to suse the same descriptor in another sheet so there is
consistency. A sample follows:

A B
Descriptor 1 AAAA
Descriptor 2 AAAB
Descriptor 3 AAAC
Descriptor 4 BBBB
Descriptor 5 BBBA

I would like to be able to put in AAAC (col B) in another sheet and have
Descriptor 3 returned to me in column A.

I want to believe this is possible, I just haven't been able to figure it out.

Shane,

VLOOKUP cannot work for me. I did fail to mention it is not sorted (I
cannot have it sorted due to the data). The INDEX, MATCH solution works
though. I tried working with one or the other but not both.

Thank you for the solution. It is exactly what I need.

Scott

"ShaneDevenshire" wrote:

Hi Scott,

You can use VLOOKUP if the code you are looking up is in the first column of
the lookup range, but as shown below your's is not. So you can use something
like MATCH and OFFSET or MATCH and INDEX or even MATCH and INDIRECT.

Suppose the table you showed below starts in cell A1

A B
Descriptor 1 AAAA
Descriptor 2 AAAB
Descriptor 3 AAAC
Descriptor 4 BBBB
Descriptor 5 BBBA

is in Sheet1 and on Sheet2 in cell A2 you enter AAAC and you want Descriptor
3 to appear in cell B2, then in B2 enter the formula

=INDEX(Sheet1!$A$1:$A$5,MATCH(A2,Sheet1!$B$1:$B$5, ))
--
Thanks,
Shane Devenshire


"Scott A" wrote:

I have multiple sheets in one XLS file. Some 4-character codes repeat on
different sheets. I am trying to figure out how I can reference one sheet's
4-character code to suse the same descriptor in another sheet so there is
consistency. A sample follows:

A B
Descriptor 1 AAAA
Descriptor 2 AAAB
Descriptor 3 AAAC
Descriptor 4 BBBB
Descriptor 5 BBBA

I would like to be able to put in AAAC (col B) in another sheet and have
Descriptor 3 returned to me in column A.

I want to believe this is possible, I just haven't been able to figure it out.