Hi everyone,
I have a question. If I have 2 sheets in excel and on the 2nd
sheet I have a table that is divided in to few catagories, lets say Profile,
Item, and length. what I'm trying to do is on sheet 1, I want to type the
Item in one cell then it automatically display the Profile and the length in
to two different cells. I tried to play with the formulas a little but I
could not figure it out.

You will need to use an INDEX/MATCH combination - both described in
Excel Help.

Hope this helps.

Pete

On Apr 11, 5:17*pm, karim wrote:
Hi everyone,
* * * * * I have a question. If I have 2 sheets in excel and on the 2nd
sheet I have a table that is divided in to few catagories, lets say Profile,
Item, and length. what I'm trying to do is on sheet 1, I want to type the
Item in one cell then it automatically display the Profile and the length in
to two different cells. I tried to play with the formulas a little but I
could not figure it out.

Assuming a table on sheet 2 in this format
Profile Item Length
1 2 3
4 5 6
7 8 9

Put a 1 in a1 andd this formula in b1 and drag right to c1
=VLOOKUP($A$1,Sheet2!$A$1:$C$4,COLUMN(B1),FALSE)

for a 1 in A1 it will return 2 and 3 in B1 - C1

Mike



"karim" wrote:

Hi everyone,
I have a question. If I have 2 sheets in excel and on the 2nd
sheet I have a table that is divided in to few catagories, lets say Profile,
Item, and length. what I'm trying to do is on sheet 1, I want to type the
Item in one cell then it automatically display the Profile and the length in
to two different cells. I tried to play with the formulas a little but I
could not figure it out.

The OP wanted to enter the Item on the other sheet, Mike , then pull
Profile and Length.

Pete

On Apr 11, 5:29*pm, Mike H wrote:
Assuming a table on sheet 2 in this format
Profile Item * *Length
1 * * * 2 * * * 3
4 * * * 5 * * * 6
7 * * * 8 * * * 9

Put a 1 in a1 andd this formula in b1 and drag right to c1
=VLOOKUP($A$1,Sheet2!$A$1:$C$4,COLUMN(B1),FALSE)

for a 1 in A1 it will return 2 and 3 in B1 - C1

Mike



"karim" wrote:
Hi everyone,
* * * * * I have a question. If I have 2 sheets in excel and on the 2nd
sheet I have a table that is divided in to few catagories, lets say Profile,
Item, and length. what I'm trying to do is on sheet 1, I want to type the
Item in one cell then it automatically display the Profile and the length in
to two different cells. I tried to play with the formulas a little but I
could not figure it out.- Hide quoted text -

- Show quoted text -

I did try to use the help for using Index/Match but I still can't get it to
work.
what I did was, I went to Formulas, then clicked on Lookup & Reference, then
choose VLOOKUP.
and for the lookup_value I put the cell were I type the Item #
for the Table_Array I put the table I created in sheet 2 that has profile,
item, and length
and for Col_index_num and also put the table in sheet 2.
so I'm not sure if that is right or i'm missing something.

"Pete_UK" wrote:

You will need to use an INDEX/MATCH combination - both described in
Excel Help.

Hope this helps.

Pete

On Apr 11, 5:17 pm, karim wrote:
Hi everyone,
I have a question. If I have 2 sheets in excel and on the 2nd
sheet I have a table that is divided in to few catagories, lets say Profile,
Item, and length. what I'm trying to do is on sheet 1, I want to type the
Item in one cell then it automatically display the Profile and the length in
to two different cells. I tried to play with the formulas a little but I
could not figure it out.

I wish people would give details of the layout and quantity of data on
their sheets, then the formula can be customised to suit their
circumstances without any further changes. In the absence of that,
then, let's go with Mike's suggested layout in Sheet2:

Profile Item Length
1 2 3
4 5 6
7 8 9

and in A2 of sheet1 you enter a value for the item you are tring to
find. Put this formula in B2 of that sheet to retrieve the profile:

=INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:C$4,0), 1)

and this one in C2 to retrieve the length:

=INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:C$4,0), 3)

Change the cell references to suit your data.

Hope this helps.

Pete

On Apr 14, 8:40*am, karim wrote:
I did try to use the help for using Index/Match but I still can't get it to
work.
what I did was, I went to Formulas, then clicked on Lookup & Reference, then
choose VLOOKUP.
and for the lookup_value I put the cell were I type the Item #
for the Table_Array I put the table I created in sheet 2 that has profile,
item, and length
and for Col_index_num and also put the table in sheet 2.
so I'm not sure if that is right or i'm missing something.



"Pete_UK" wrote:
You will need to use an INDEX/MATCH combination - both described in
Excel Help.

Hope this helps.

Pete

On Apr 11, 5:17 pm, karim wrote:
Hi everyone,
* * * * * I have a question. If I have 2 sheets in excel and on the 2nd
sheet I have a table that is divided in to few catagories, lets say Profile,
Item, and length. what I'm trying to do is on sheet 1, I want to type the
Item in one cell then it automatically display the Profile and the length in
to two different cells. I tried to play with the formulas a little but I
could not figure it out.- Hide quoted text -

- Show quoted text -

Sorry, slight amendment needed:

B2: =INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:A$4,0), 1)

C2: =INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:A$4,0), 3)

Hope this helps.

Pete

On Apr 14, 9:32*am, Pete_UK wrote:
I wish people would give details of the layout and quantity of data on
their sheets, then the formula can be customised to suit their
circumstances without any further changes. In the absence of that,
then, let's go with Mike's suggested layout in Sheet2:

Profile * *Item * *Length
* * 1 * * * * *2 * * * 3
* * 4 * * * * *5 * * * 6
* * 7 * * * * *8 * * * 9

and in A2 of sheet1 you enter a value for the item you are tring to
find. Put this formula in B2 of that sheet to retrieve the profile:

=INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:C$4,0), 1)

and this one in C2 to retrieve the length:

=INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:C$4,0), 3)

Change the cell references to suit your data.

Hope this helps.

Pete

On Apr 14, 8:40*am, karim wrote:



I did try to use the help for using Index/Match but I still can't get it to
work.
what I did was, I went to Formulas, then clicked on Lookup & Reference, then
choose VLOOKUP.
and for the lookup_value I put the cell were I type the Item #
for the Table_Array I put the table I created in sheet 2 that has profile,
item, and length
and for Col_index_num and also put the table in sheet 2.
so I'm not sure if that is right or i'm missing something.

"Pete_UK" wrote:
You will need to use an INDEX/MATCH combination - both described in
Excel Help.

Hope this helps.

Pete

On Apr 11, 5:17 pm, karim wrote:
Hi everyone,
* * * * * I have a question. If I have 2 sheets in excel and on the 2nd
sheet I have a table that is divided in to few catagories, lets say Profile,
Item, and length. what I'm trying to do is on sheet 1, I want to type the
Item in one cell then it automatically display the Profile and the length in
to two different cells. I tried to play with the formulas a little but I
could not figure it out.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

thank you so much.

"Pete_UK" wrote:

Sorry, slight amendment needed:

B2: =INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:A$4,0), 1)

C2: =INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:A$4,0), 3)

Hope this helps.

Pete

On Apr 14, 9:32 am, Pete_UK wrote:
I wish people would give details of the layout and quantity of data on
their sheets, then the formula can be customised to suit their
circumstances without any further changes. In the absence of that,
then, let's go with Mike's suggested layout in Sheet2:

Profile Item Length
1 2 3
4 5 6
7 8 9

and in A2 of sheet1 you enter a value for the item you are tring to
find. Put this formula in B2 of that sheet to retrieve the profile:

=INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:C$4,0), 1)

and this one in C2 to retrieve the length:

=INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:C$4,0), 3)

Change the cell references to suit your data.

Hope this helps.

Pete

On Apr 14, 8:40 am, karim wrote:



I did try to use the help for using Index/Match but I still can't get it to
work.
what I did was, I went to Formulas, then clicked on Lookup & Reference, then
choose VLOOKUP.
and for the lookup_value I put the cell were I type the Item #
for the Table_Array I put the table I created in sheet 2 that has profile,
item, and length
and for Col_index_num and also put the table in sheet 2.
so I'm not sure if that is right or i'm missing something.

"Pete_UK" wrote:
You will need to use an INDEX/MATCH combination - both described in
Excel Help.

Hope this helps.

Pete

On Apr 11, 5:17 pm, karim wrote:
Hi everyone,
I have a question. If I have 2 sheets in excel and on the 2nd
sheet I have a table that is divided in to few catagories, lets say Profile,
Item, and length. what I'm trying to do is on sheet 1, I want to type the
Item in one cell then it automatically display the Profile and the length in
to two different cells. I tried to play with the formulas a little but I
could not figure it out.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

You're welcome.

Pete

On Apr 14, 9:51*am, karim wrote:
thank you so much.



"Pete_UK" wrote:
Sorry, slight amendment needed:

B2: * =INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:A$4,0), 1)

C2: * *=INDEX(Sheet2!A$2:C$4,MATCH($A2,Sheet2!A$2:A$4,0) ,3)

Hope this helps.

Pete